Looping through repeating fields and updating another file

[stockingup]

Hi,

I am currently working on a project for a company I am employed at, and was wondering if someone could help me out with a problem I'm having.

I have 2 tables - ProductKit and Inventory.

The idea is that 20 products are listed in Product Kit, with 3 fields:

Serial number

name

quantity

The table Inventory has the Exact same fields.

I need the script to change the Quantity in Inventory to be the Quantity in ProductKit, and do this for all 20 products.

I have tried looping through a repeated field with 20 repeats, but I can always only get the first quantity to change.

My problem seems to be that I don't know how to reference the record in the Inventory with the same serial number. I have been using an if statement to make the comparison, but that isn't really want I need I dont think...

This is quite urgent, so all replies would be greatly appreciated. (I have simplified my problem substantially for this post, its actually part of a 15 table database, but this is really the only problem I'm having).

thanks in advance!

Jason V.

[Søren Dyhr]

Your skills in relational designs are not quite there "intermediate" - although they easily might be so with spreadsheets!

Relational theory is there to prevent exactly what you ask for here - synchronization. Repeating fields as such are breaking something very basic, and should only be used for utility purposes.

Is Filemaker then approach wise, an attempt to abide to relational theory - by and large yes! This means you use the tool to store each atomic piece of information each in it's own unique location, and then reference it...

Unfortunately does the simplification of your problem only serve to give you away - you talk about 15 tables where at least two of them have the same fields, this is a major blunder in your design!

We can't really tell what the purpose and context are here, but the naming of your tables suggest perhaps even a recursive structure - what tool or rules have you used to come up with the tables you think you need, how is the fields structure established??? Unfortunately can't we be sure this part of the design is done properly yet - hence the apparent need to do something to repeating fields?

--sd

[stockingup]

Unfortunately not only did I not understand half of what you just said, but you didn't actually make any attempt to even help me.

They have similar fields, but only one is actually entered. The rest is populated from another database with all the product information. I still don't understand how I should go about updating the inventory table with the quantities from the Product kit table.

All help would be greatly appreciated.

[Søren Dyhr]

I said you shouldn't update at all - reference the data via relations!

...and of course havn't I helped you yet, you need to explain how you reached to the needed fields and tables you claim to need. What method are they established under ... since they seems to differ from usual practice. I'm not saying that you need to pull the entire scenario thru the normal forms if you intuitively can make the shortcut ... just that it takes a lot of practice to do so!

--sd

Edited June 30, 2008 by Guest

[stockingup]

I understand what your saying now regarding referencing the data. The problem is that in actual fact I dont want to replace the data per say, but rather deduct a set amount from the data. Lets say that the quantity for each of the 20 products in the Produt kit table was 10, I then want to deduct that value, for each product, from the inventory table which has the total stock. How would referencing make this possible, considering I may be filling out the Product kit form multiple times on different days/ times...

The structure as it stands is mainly comprised of the following:

Tables:

Product Information

Engineer Information

Transactions

Inventory

Product Kit

Transactions is linked to Product infomration via Product Serial number, and to Engineer information via engineer Identification number. It is only linked to the Inventory via a script that currently updates the stock.

Product Kit is then linked to Inventory aswell via Product Serial number.

I need a way of performing addition/subtraction of a set quantity from the inventory table after a script is run.

[Søren Dyhr]

Lets see if I get this correctly, you wish to know how many sets or kits you can make out of the current stock levels of each component?

If it is scrutinize this:

http://www.jonathanstark.com/downloads.php#inventory_example

...and especially the article of his here:

http://www.jonathanstark.com/recursive_data_structures.php

--sd

[stockingup]

Thats not really what I want. Its actually pretty much the opposite. I want to take the values input in the Product Kit page, and deduct them from the inventory. So that if I then check the inventory, the value is less than it was previously.

[Søren Dyhr]

Ok a classic many2many relation, you do not deduct anything form the inventory, the storage of the "pull" is in the transactions table (join) ... the storage level is established via unstored aggregate functions looking at the related set of records the ones with identical ID's.

Now depending on the scaling of the solution, could your aggregate functions be too slow an option, and you change the transaction table to make lookup last to get the running sum stored in each transaction, so the only record you need to call in order to know what the actual level would be is the last of each transaction type.

Take a look at JMO's join table solution here:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

...and combine it with this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000333

--sd

[mr_vodka]

I would check out this article...

http://my.advisor.com/doc/17403

There are pluses and minuses to a transactional model as with all others but its worth taking a look into.