Value List Orphan

[David Poiron]

I am working on a POS solution. I have a table for products, brands and of course sales, amongst other tables. The product table is a list of generic items for retail (shoes, shirts, jewelry, etc.). When setting up a brand, I have a checkbox field that lists all of the products based on a value list of all products, so the store owner can select which products they carry for each brand.

During the making of a sale and after entering a brand, only the checked items for that brand show up in a product drop-down list via a brand-product relationship I set up for this purpose. So far so good.

Here is the problem. If a brand is set to say have "shoes" selected, and then store owner deletes the "shoes" record in the product list because they no longer carry shoes, the brand checkbox list does not show shoes for that brand any longer but "shoes" still shows up in the menu list for the sale item.

I set up a script to cycle through each brand to delete the orphaned "shoes" text (or whatever the product may be) when the store owner clicks a delete product button, but there must be a better way to do this. I'm not sure my find and replace works 100% of the time anyway.

Any ideas?

[David Jondreau]

I don't understand your exact issue with regards to the menus. What is the checkbox value list based on? What is the product drop down value list based on?

Even without knowing that, I'd say better way would be to not allow a user to delete a product if it's been sold. Rather have them mark it "inactive" or such.

Even better, would be to store all this value list information in tables. Brands, Products, and Product Brand Join would be a good start.

[David Poiron]

Sorry, maybe my explanation was not detailed enough.

The checkbox value list is the full product list field on the brand table, based on a product field in a product table. The drop down (value) list is a product field in a sale item table and is based on the checkbox product field in the brand table (shows a potentially reduced list of products – whatever is checked in the checkbox on the brand table).

Maybe there is a better way for the owner to select the products for that brand, than a checkbox field?

[comment]

I think the direction needs to be reversed: instead of selecting the products that belong to a category, assign each product to a category.

[David Poiron]

I do not think this achieves the interface criteria the owner is looking for. They want to be able to look at one brand and select the products currently available for that brand. I think you are suggesting that they look at each product, and assign the brands that will carry each product which is not intuitive for this situation.

Maybe there is a way to "refresh" the field to only use the checkbox items of the new list of products (now reduced by one record as we just deleted it)?

[comment]

The interface is not the issue here. The data structure is incorrect: if the relationship between categories and products is one-to-many, then the foreign key needs to be in the child table - see also:

http://fmforums.com/forum/showtopic.php?tid/202792/

You can build a similar interface for the user, for example by showing all products in a portal and allowing them to click-select the products that belong to the current category.

[David Poiron]

Maybe my terminology is incorrect, but I think this is a many-to-many relationship? Brands can carry more than one product, and products can be carried by more than one brand.

[comment]

Then, strictly speaking, there should be a join table between them. If the relationship between products and the join table is set to automatically delete join records when a product is deleted, the category record will "see" the change automatically.

You could get by with using a checkbox instead - but I would still put the checkbox in the Products table. This way you can stipulate that a product cannot be deleted if the checkbox is not empty.

It is possible do the same thing even with your current structure (a product cannot be deleted if it has related records in Categories), but it will be more difficult for the user to solve the problem. Maybe you should consider a scripted deletion, that goes to the related categories and removes the product from their lists.

[David Poiron]

A scripted deletion is exactly how I am addressing the current situation. I was just hoping for a more automatic (not scripted) solution.

I'll think about your comments regarding the join table. Maybe a little over my head at the moment - maybe it is just the end of the day! Thanks.

[comment]

Maybe this can be helpful:

http://fmforums.com/forum/showpost.php?post/233897/