Jump to content
Sign in to follow this  
David Poiron

Value List Orphan

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Maybe this can be helpful:

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.