Thank you for your post.
Rather than looping through, copying, pasting, etc., consider importing the Excel spreadsheet into an "Import" table that is related to the catalogue table based on "catalogue number". Assuming the items in stock are stored in a field named "Stock", then include in the "Import" table a calculation with the formula: Sum (Catalogue::Stock)
Assuming there is only one related record in the Catalogue table, the one Stock value is displayed. If there are more than one record with the same "catalogue number", then those values will be summed.
At this point, you can perform a Find for those records greater than 0 to show all items currently in stock. Search for 0 to find all records where the item is not in stock. Perhaps find for less than 0 to see if items are back ordered?
Let me know if you need additional clarification.
What you said makes sense, but I
'ma little unclear.
So I would have another table in my "Manage Database" window, with only the fields that appear in the XL sheet catalogue + a calculated field with the calculation: Sum (Catalogue Number:
:Qty.) ? "Qty." is the name of the field with the amount of copies we have. Catalogue Number is obviously the field containing the catalogue numbers of each record.
In the relationships window these two tables are linked by an = line using the catalogue field.
When I try to do this and click OK it says "This Specific Table Cannot be found"
Your screen shot shows the related table name Assets. Therefore, the correct calculation would be:
That is, drop "Catalogue Number" from the calculation.
Seems to be working now!