Auto Update of duplicate records in portal

[paolobkk]

Hello everyone,

I have 2 related tables: 1 with the date and ID of the inventory (Inventory check) and another with all the info for each product (line inventory check).

In the layout of the table "Inventory check" I set up a portal from "line inventory check" with a field (code_products) where the user can select a code from a drop down value list with all the products codes and another field to add the number of items in inventory.

I also made an autoenter calculation field in "line inventory check" (code_products & IDinventory) with a validation as "Unique Value", In this way the same products will not be committed twice in the same inventory day.

The problem is that when this happen, the user must scroll all the portal looking for the product already committed in order to update the quantity, or he must go to the layout of "line inventory check" and perform a find for that product within the same inventory ID.

I'm trying to make a script triggered from "object exit" in the "code_products" field of the portal that will check for duplicates (maybe using the field "code_products & IDinventory") and in case the item is already in the list will pop a dialog box where the user will insert the number of items to add for that product.

I've being considering global fields and variables but I still can't find the right way to make it work, any suggestion is greatly appreciated.

Thanks

Paolo

FMPA 11 on Win 7 running from FM server 11 on Mac mini Server.

[comment]

To check for duplicate entries, you could define a self-join relationship of the lines table. However, I am not sure this is a good method to employ when counting inventory. If user finds 3 shirts on the shelf and another 10 shirts in a box, then IMHO that's exactly what they should enter - leaving a clear trail of what they did and how. It's easy to summarize the entries by product when looking at the results.

[paolobkk]

To check for duplicate entries, you could define a self-join relationship of the lines table. However, I am not sure this is a good method to employ when counting inventory. If user finds 3 shirts on the shelf and another 10 shirts in a box, then IMHO that's exactly what they should enter - leaving a clear trail of what they did and how. It's easy to summarize the entries by product when looking at the results.

Could you please explain quickly what is "IMHO"?

For the location of the products the users are writing on a different field all the locations for each product and is not necessary to have an history trail.

I would like to avoid to have more entries for the same product because the final list have to be displayed on different layouts and there are also calculations involved, it's important to have one entry only for each product.

[comment]

IMHO

the users are writing on a different field all the locations for each product

Well, that's between you and your users. When I count something, I often get mixed up in the middle - and using your method, I would have to start all over.

From the point-of-view of data structure, it would be best to have ONE field for quantity, one for the product code, and one for the location - and it makes no difference whatsoever how many records (including duplicates in terms of product and location) there are. You will still be able to produce any type of report, including calculations - probably much more easily than with multiple quantity fields.

[paolobkk]

all the products are always "on the move" and the location is always updated despise the inventory check. Let's say the inventory check is also a "location check" update. But that is not the main issue.

The inventory is related also to the catalog table, where each product has the location field (the one that is always updated) and in the catalog layout there is a portal of all the inventory checks sorted by date, for each product (from "line inventory check").

I would like to limit the entries of the inventory from the beginning by avoiding double entries, because is not necessary to make extra summarizing jobs and also it would display double on the portal in the catalog layout.

I could make another TO of "line inventory check" and create a second portal on the layout of "inventory check" with 1 row, using a filtered relation in a way to show me eventual double entries... but I got stock in scripting :unsure: