Copy/Import records from Table A to related Table B via script

[Stu412]

Hi there

I've been banging my head all day, several days in fact, to create a sub summary report which can reuse records, have a part appear only once and allow me to assert more behaviour over the parts themselves.  To complicate the matter it's in a crosstab format as well.

I've seen on other posts that what developers do occasionally when preparing reports is to create a temp table of sorts to either store just what records are needed, or to store them in the way they're needed as well.

The solution I have at the moment uses lots of summing up and calculations and is intolerably slow to load locally (10 seconds at worst) although it does give me what I need.  I obviously want to improve this so ultimately a second table related to the parent with just what I want, just how I want it sounds good.

At this point I will add I may have this completely wrong - apologies, it's an idea I'm throwing around!

The whole reason I'm looking at this is because I cannot get my sub summary report to behave the way I need it, which may be a failing on my part, but it's a pretty complex report as I've said.

 

So, in concept, is this whack or normal?

[ggt667]

The way to make this kind of reports faster is to avoid calculation fields, and do your calculations in a regular number field with a validation against a modified timestamp field.

Such an approach will lean all the calculations on the client itself not wasting bandwidth, supposedly.

Example calculation field made like a number field this only works internally on the fields in the same table

 

For an invoice row this may be the calculation in question

extendedPrice( calculation ) = unitprice * amount

 

replace with:

extendedPrice( number ) = case( modifiedTimestamp; ( unitprice * amount ); 0 )

 

This tweak made noticable difference with files stored on FM11SA over WAN; not sure if this still gives a performance boost with FM14S

Edited September 9, 2015 by ggt667

[Stu412]

Hi ggt667

I think I see what you're saying here, but so I'm clear, let me explain it back to you.  

Are we basically saying that Filemaker won't evaluate the calculation if the numbers that go into the calculation haven't changed?

Or have I got that completely wrong?

Thanks again

[ggt667]

My workaround makes the calculation happen when modifiedTimestampHost is changed; opposed to when opening a layout that has the field.

The headache is that this workaround only works for fields in the same table.

Edited September 14, 2015 by ggt667

[comment]

do your calculations in a regular number field with a validation against a modified timestamp field.

I suggest you do no such thing.

If the field is a stored calculation (as in the example given), then it evaluates only when one of the referenced fields has been modified. Adding a Modified field to the list of referenced fields only increases the number of evaluations (now the field will evaluate whenever any field in the same record has been modified).

If the field is an unstored calculation, then forcing it to be stored (by turning it into a Number field that re-evaluates only when a field in the same record has been modified) will cause it not to re-evaluate when a referenced field in another record was modified. Every time that happens, you will be looking at incorrect data in the field.