Value List for Field other than ID

[x14607]

I've changed my system around so that all primary keys are now autoentered, not modifiable. So this way I have a ProductID for each product. There is a related table , Resources, which lists resources required to produce each product, and a join table, ProductResoureces, with ProductID, ResourceID, and qty.

Now, I have a ProductResources portal on the Products layout. This allows me to view and add/delete resources needed for each product, via a value list to select the resource.

Here is the problem:

I need to select the Resource ID (and then the resource's title will pop up of course in that field)... BUT - I don't want to display the ResourceID (just a number) at all. I only want to display the Resource TITLE and also to be able to select the resources by TITLE only. The ID I use is actually the creation timestamp, and so it's not something I can just display alongside the Titles... I need to somehow hide it alltogether...

The point is that somehow the ResourceID has to be selected, as that is what the relationship is based on.

I'm sure this is simpler than I think, but I'm still learning =)

Furthermore, I need these items to somehow be listed by the most commonly used to least commonly used. I can figure out a field to calculate 'usage' by a simple Count function. But as far as implementing this into my value list, I'm stuck.

By the way, it doesn't necessarily have to be a value list, it just seems to be a simple and efficient way of selecting an item from those on a table.

Any advice?

Thanks

[Wim Decorte]

Selecting from a value list can only return whatever you selected the first column of the VL to be. If you want to return the ID, you have to show the ID.

Other ways around it:

- make a VL with just the names, and have a relationship from name to name look up the ID for you. Not my favourite because names might not be unique and can be changed

- since you're using 7, pop up a new window with a list view showing just the names, when they select a resource record, grab the ID, close the window and store the ID in the product's resource portal record.

[x14607]

I think that might work for me, the new window idea, but I'm not sure how to do exactly as you said. I do know how to open a new window, and to create a list type layout, but that's the extent for me. I'm not quite sure how to a) allow them to select an item on the table, : have that selected name pull up the ID and set it into the field in the portal. I'm very interested to learn how though.

Also for the sorting, I would want that table to sort by the most commonly used items, so I'm assuming just put that in as a sort, and have it perform the sort when it opens the new window. Correct?

Thanks for the input though - I definitely appreciate it and look forward to learning how to complete this.