Modifying multiple records based on a repeating field

[Brendan O'Naughton]

So I used the "Business Productivity Kit" as a basis for designing a solution for tracking items from Purchasing into Inventory and then into Sales. Each are set up as separate files, with relationships defined between the "Product Code" fields in each file. In "Inventory" there is one record for each unique Product Code, containing information about the product (including the quantity currently in stock). In "Purchasing" and "Sales", the Product Code field is repeating, to accommodate purchases and sales of multiple products. I have an "Update Inventory" button in these two databases which is supposed to step through the repeating field, and for each repetition update the "Product Quantity" field in the "Inventory" file based on the current repetition of the "Product Quantity" field in the "Purchasing" or "Sales" file.

Problem is, it doesn't work What happens is this. Say I have a Purchase defined that contains an order for 10 of Item 1 and 5 of Item 2. When I run my update inventory script, the Product Quantity of both Item 1 and Item 2 in the Inventory file get increased by 15. Here's the relevant portion of my script:

Set Variable [$counter; Value:1]

Loop

Exit Loop If [isEmpty(Purchasing::Product Code[$counter])]

Set Field[inventory::Amount in Stock; Inventory::Amount in Stock + Purchasing::Product Quantity[$counter]]

Set Variable[$counter; Value:$counter + 1]

End Loop

I think what's happening is that for each iteration of the loop, it's adding the value in the current repetition of "Product Quantity" to the "Amount in Stock" field of all of the records in the found set in "Inventory", not just the field that matches the current repetition of "Product Code". Can anyone offer some advice on solving this problem? Thanks!

[IdealData]

There is a specific function to address the individual repetitions in a repeating field:

GetRepetition(repeatingField;number)

In your instance you'll need to use $counter as the repetition number in the above formula

Thre's more in the Help system.

[Brendan O'Naughton]

This is not the problem. I am addressing the current repetition of the field. These two lines should be identical in function:

SetField[inventory::Amount in Stock; Inventory::Amount in Stock + GetRepetition(Purchasing::Product Quantity;$counter)]

and

SetField[inventory::Amount in Stock; Inventory::Amount in Stock + Purchasing::Product Quantity[$counter]]

The latter is in my script as-is, the former is based on your suggestion. Both are functionally the same, they're just different ways of accessing the same data. Just to make sure, I tried your suggestion and the problem does indeed persist.

To clarify: The problem, as near as I can tell, is that when I have repetitions in Purchasing, the Inventory table consists of multiple records, and when I attempt to modify "Amount in Stock" in the Inventory table, it modifies the contents of that field in all records in the inventory table, and not just the record that matches the current repetition of Product Code in the Purchasing table. Does that make sense?

[comment]

I think the problem is the meaning of "Inventory::Amount in Stock" in the context you use it.

In order to see what I mean, place a portal to Inventory on a layout of Sales. As you can see, the portal shows all products whose product code appears in the repeating field. They are all related records.

The reference "Inventory::Amount in Stock" in your script refers always to the FIRST related record. The order of the related records is as defined in the relationship. The order of product codes in the repeating field is irrelevant in this context. So, if I am not mistaken, you're always modifying the Amount in Stock of the same Inventory record (and not ALL records).

This is one reason (out of many) why repeating fields are not suitable for Invoice/Purchase line items. Change your structure so that each item is a record in a related LineItems table.