Is there a better way than using form calc fields

[crimmelcp]

Attached is a picture of an entry screen used in a payroll application in many states.
Customer wants summary of hours by craft codes (051,052,453,452,451 etc.)
to show on the screen as they enter records into an embedded browse.
Craft codes could be up to 8 characters but the summary would be only the first 3 digits. (substr(craft,1,3))

Craft codes could be as little as 10 and as many as 100.
I was testing with calculated fields on the form and it works, but I do not want to add calculated fields everytime the user adds a new craft.

The data entry screen is actually a set with a header and detail linked by sheet number. The hours for the crafts are totals of that sheet only.

I could append unique to a table each time the user changes rows in the embedded browse, but on some networks there could be up to 6-8 users entering data. The append unique would slow the application down.

Any ideas on how to show the total hours by craft code for each sheet as the user enters data. Again if the user enters a new craft code for that sheet, I do not want to have to add calculated field.

[Stan Mathews]

Re: Is there a better way than using form calc fields

Attached is a picture of an entry screen used in a payroll application in many states.
Customer wants summary of hours by craft codes (051,052,453,452,451 etc.)
to show on the screen as they enter records into an embedded browse.
Craft codes could be up to 8 characters but the summary would be only the first 3 digits. (substr(craft,1,3))

Craft codes could be as little as 10 and as many as 100.
I was testing with calculated fields on the form and it works, but I do not want to add calculated fields everytime the user adds a new craft.

The data entry screen is actually a set with a header and detail linked by sheet number. The hours for the crafts are totals of that sheet only.

I could append unique to a table each time the user changes rows in the embedded browse, but on some networks there could be up to 6-8 users entering data. The append unique would slow the application down.

Any ideas on how to show the total hours by craft code for each sheet as the user enters data. Again if the user enters a new craft code for that sheet, I do not want to have to add calculated field.

It is late for me and this may be a little out because I haven't tried it out. How about a browse of a table of craft codes and calculated fields (dbsum() or tablesum()) to the right where your totals are now? Not sure what the performance hit would be for recalculating the calculated fields based on some event.

[Tom Cone Jr]

Re: Is there a better way than using form calc fields

Charlie,

What if you use the OnFetch event of the embedded browse object to run a script that reads the current craft code & sheet number and then computes the total? The displayed result would change as the user navigates from row to row. You wouldn't show all the totals for all the craft codes in the sheet, just the total for the row that's currently selected. If performance over the LAN is an issue maybe you could keep a running total for each craft code in the sheet using an array, and change the element from the array that's displayed as the user fetches through the browse?

[crimmelcp]

Re: Is there a better way than using form calc fields

Attached is a sample entry screen using calculated fields

Trying to find a better way

Charlie Crimmel

[Peter.Greulich]

Re: Is there a better way than using form calc fields

I think Tom is on to something. Does the OnSave form event fire when you save an embedded browse record? If not, you might just put it into field rules and only fire the script when the object exists - or, use a dialog for the browse data entry (make the browse read only).

In any event (no pun intended), you might need two scripts:

1. when you first load the record: loop through the child records and assign array variables for each Craft code and for each value, and total in the script. Display as variables (or my favorite, text objects) on the form.
2. Using the OnSave event as suggested above, fire a script that appends the latest Craft code & totals, and update the form display.

Since there will be a finite number of craftcodes related to one parent record, you should be able to manage the display area on the form w/o too much difficulty. A little bit of coding, for sure, but the results will be instantaneous!

[martinwcole]

Re: Is there a better way than using form calc fields

Recently I have been helping someone with an app with large files (2.6 million records in one of the child tables and large quantities in all tables) and have been experimenting with ways to make things work faster. Calculated fields and form sort orders put a big load on the system. For calculated fields like you are describing I am experimenting with a post operation to counter fields, as for crafts. And possibly a nightly routine to reset them manually once a day.

For the record, I have discovered that it is faster to use an index for sorting and filtering than to use the form for it. But you have to experiment with how much time is added to the new record save when you add an index against how much time it saves with the form.

PS: good to see you around again Charlie - hadn't seen you here for ages!!

[crimmelcp]

Re: Is there a better way than using form calc fields

No luck yet.
The script builds the initial aaray with the craft codes, but I cannot get the hours to build correctly for each craft code.

Tried an summary operation with the result table linked in the set to the header table by sheetno.
But because I am running the operation from the form, I am having record locking problems and not getting the correct info.

Any other ideas?
Charlie Crimmel

[Steve Workings]

Re: Is there a better way than using form calc fields

What Tom and Peter said.

Use the OnSave event and Xbasic for a little looping through the detail file. Use an appropriate index so you can build a value like this:

t = table.current()
key = t.myfieldvalue

tDetail = table.open("whatevertable")
tDetail.index_primary_put("IndexName")
tDetail.fetch_find(key)
KeyValue = 0
While tDetail.fieldname = key .and. .not. tDetail.fetch_eof()
 KeyValue = KeyValue + tDetail.OtherFieldName
 tDetail.fetch_next()
End while

This runs faster than you can imagine, and several people doing data entry will hardly affect it.

Use the resulting values for display.

Another approach is even simpler, and potentially faster.

filter = "SomeField = SomeValue"
MyVar = tablesum("Tablename", "Field", filter)
(I think that's the syntax)

I just did a report that gathers up about 60 different values, mostly with this latter approach, and it takes no time at all.

I know these are a bit general, but these methods, probably with modifications for your needs, can get a lot of values fast.