Jump to content
Sign in to follow this  
Joe_Schmo

Best Practices for relationships w/ Unique ID and lookup Fields

Recommended Posts

I'm trying to relate an INVENTORY table to an INVOICE table using a LINE ITEM table. All records in the INVENTORY table have a unique ID number, which seems like the best field to use to base the relationship on. The problem is, the users have no need to ever see that number and I don't want it taking up space on the LINE ITEM portal on the INVOICE layout.

If I DO add the ID to the portal, then I can get the other fields to autofill using a lookup. But without the field on the portal, I can't find a way to let the user select the record by choosing the INVENTORY::ItemName and have the price populate, since that's not the field I use for the relationship. And if I change the relationship that just seems to make the unique ID pointless and could cause problems later if the ItemName is changed or another item has the same name.

My current relationship is:

INVENTORY:ID = LINE_ITEMS:ID

LINE_ITEMS:INVOICE_NUM =I NVOICE_NUM

-with only one occurance of the LINE_ITEMS table on the relationship graph.

I have the relationship set to allow creation of records in the LINE_ITEMS table from either of the other two tables. And I have a value list assigned as a drop down that shows records from the INVENTORY table. So the user can select an item in the portal on the invoice but the price won't look up unless I add the ID field to the portal and select the right record via the ID instead of the name.

So, how do you base a relationship on a primary key and use that to select a record in a portal if you can't see that primary key field?

Share this post


Link to post
Share on other sites

Assuming your item names are unique (and if you're going to use them as indicated, they'd better be validated as such), you can define your value list to use values from INVENTORY:ID, also displaying values from INVENTORY::ItemName and check the option to 'Show values only from second field'.

Share this post


Link to post
Share on other sites

Assuming your item names are unique (and if you're going to use them as indicated, they'd better be validated as such), you can define your value list to use values from INVENTORY:ID, also displaying values from INVENTORY::ItemName and check the option to 'Show values only from second field'.

I was just playing around with that as a possible solution. Have you seen this situation before, and is that the common way around it? If I make the INVENTORY::ItemName field validated to be unique then that will work but it negates the purpose of the Primary ID field. I thought I was just missing something and there was some way to do this without the user ever seeing the primary ID.

Share this post


Link to post
Share on other sites

If I make the INVENTORY::ItemName field validated to be unique then that will work but it negates the purpose of the Primary ID field.

No, it doesn't. You are still entering the product's ID, not its name. This means you can rename a product without breaking the link to all of its existing line items.

is that the common way around it?

This is not a workaround; the feature to 'Show values only from second field' is designed to hide the ID while selecting, precisely for the reasons stated in your opening post.

As for being common or not, I believe it is very common - if you are using a value list to select the product (there are other methods).

Share this post


Link to post
Share on other sites

No, it doesn't. You are still entering the product's ID, not its name. This means you can rename a product without breaking the link to all of its existing line items.

Ok, well that was my concern so that's good to know.

I'm still not quite sure how to use this method though, without having the ID as a field on the portal. I can get it to work if the user is selecting the ID from the value list, and I can hide that ID in the value list, but the ID field itself still has to be on the portal right?

Share this post


Link to post
Share on other sites

how to use this method though, without having the ID as a field on the portal

Try using a pop-up menu instead of a drop-down.

Another option is to place the Name field from the Products table over the drop-down and make it non-enterable.

Share this post


Link to post
Share on other sites

Another option is to place the Name field from the Products table over the drop-down and make it non-enterable.

That's pretty much what I ended up doing. The ID field is just before the item name field. The item field is uneditable (it's a look up from the ID), and when you click in it, a script selects the ID field instead. So you always edit the ID field and the item name gets looked up along with the price.

Thanks for the suggestion :)

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.