Most efficient way inserting/creating many records in other table?

[gczychi]

Hi there,

for each record in table "Beleg" I need to create 1-5 records in table "Buchung". How can I easily create records in another table (within a script) while remaining in the first table? Switching layouts is way too slow when you have lots of records.

An answer for this first question would be cool. However, this begs for another question: What about the 1-5 records that need to be created would delete themselves when the user makes changes to the records in the first table? Ideally, a commit record in "Beleg" should automagically insert, correct or delete the corresponding records in "Buchung". BTW, the table "Buchung" will not be edited/changed in any way by the user, only displayed/exported. I hope I can make myself clear :-) See example below.

Thanks a lot for any help!

Gary

 

Example clarifying the second question:

One record in "Beleg":   idBeleg1   item1   value1   value2   value3   valueN   count1_5

Lets say:   count1_5 = 1----

Corresponding record in "Buchung":   idBuchung1 idBeleg1 Beleg::item1 Beleg:value1

Another record in "Beleg":   idBeleg2   item1   value1   value2   value3   valueN   count1_5

when count1_5 = 1-3-5

idBuchung1 idBeleg2 Beleg::item1 Beleg:value1

idBuchung2 idBeleg2 Beleg::item1 Beleg:value3

idBuchung3 idBeleg2 Beleg::item1 Beleg:value5

Yet another record in "Beleg":   idBeleg3   item1   value1   value2   value3   valueN   count1_5

when count1_5 = 1--4-

idBuchung1 idBeleg3 Beleg::item1 Beleg:value1

idBuchung2 idBeleg3 Beleg::item1 Beleg:value4

 

When the user changes for example Beleg::value1 in record 1 from $22 to $33, all value1 in "Buchung" relating the current record in "Beleg" will change. Thats cool.

But, when count1_5 changes, it would be way cool if the corresponding records in "Buchung" could automatically be deleted/inserted.

 

 

P.S.: It would be so easy using sql (at least for the first problem), but I don't want to carry a plugin with me all the time (FileMaker's ExecuteSQL doesn't do INSERT).

[comment]

Re your first question: is this a one-time conversion, or do you plan to do this periodically? If the latter, why?

Re your second question: what you describe is a violation of normalization rules. Why would you want to store your data in two different places, increasing the complexity and risking a possible discrepancy? You should use only the related table records and discard the numbered fields in the parent table.

[gczychi]

Hi & thanks.  

First: The data in the first table changes quite a bit and with it not only the data in the fields (easy relationships) but also the amount of records in the second table.

Second: You are right, the only thing that is needed is a different view on a subset of the data that already exists somewhere in the first table. BUT I doubt that FileMaker can do that (at least I have no clue how that would be possible) because of the changing amount of rows involved.

The second table simply is to create a couple of (sub-)summary reports and I believe there is no other way than to have all the data & records in the second table. When you look at the example, I need to sort i.e. value1, value2, valueN, … , then calculate the (sub-)totals and then display everything (in a sub-summary report).

Any ideas?

Thanks,

Gary

 

[comment]

 the only thing that is needed is a different view on a subset of the data that already exists somewhere in the first table. BUT I doubt that FileMaker can do that (at least I have no clue how that would be possible) because of the changing amount of rows involved.

​No, it can't - but that's exactly why you should use another table, and use only it. I cannot see why you would need the extra stage of multiple fields in the parent table. Let users enter their data directly into a portal.

[gczychi]

Portals are clumsy, especially when data entering speed and versatility (sorting, for example or entering new rows in the middle) is essential.

And you're right in the first place. I should have thought about the data model more thoroughly or used a NoSQL approach right from the start. I'm in too deep making these choices now. So, my best guess is indeed to use the SQL plugin to manage the records of the other table. That should be quick enough for the user to barely notice that during data entry.

Thanks a lot for your help.

Gary

 

[comment]

Portals are clumsy, especially when data entering speed and versatility (sorting, for example or entering new rows in the middle) is essential.

​I don't agree. But I will not discuss this in abstract. Especially not the "entering new rows in the middle" part. And how on earth do you enter something "in the middle" when all you have is 5 fields?

In any case, if you think that managing sync between 5 fields (or sets of fields) and a related table is going to be less clumsy ...