Value list shows field A value but enters field B

[wplate]

I have a popup menu for inventory items, the value that must be inserted into the line item table is the INVENTORY_ID, but I would like the user to see in the popup menu the INVENTORY_NAME to make it more user-friendly.

INV_id is a unique value, but INV_name might not be unique, that is why I cannot key to the INV_name.

So I need the value list to display one value but enter into the field another value. Help! :-) Thanks.

[RalphL]

Consider using a portal to show your parts. Have a button in the portal row that puts the part number into the field that you were going to have the popup. You can display much more information in a portal than in a popup, i.e., quantity on hand.

[wplate]

I am using a portal, it is a portal in the INVOICES table that looks at the LINE ITEM table. In the LINE ITEM table I have the Inventory ID field that uses the value list I'm trying to change.

[RalphL]

I am suggesting a portal that shows the inventory. This might be on another layout. The script does the work of puting the part number into your invoice in the correct portal row.

[wplate]

Hmmm, scripts. I'm not a scripter.

Are you suggesting that the popup menu shows the available inventory, and next to it I could have an "apply" button that copies the appropriate field into my line item? That's not a bad thought, I'll give it a go.

[RalphL]

No I am saying that a portal could show the quantity on hand. A popup is limited to 2 columns and no buttons.

You will never learn scripting any younger.

[wplate]

I don't need to show quantity on hand, we don't care about that. I just need the user to select what the customer is buying. The current system has been fine, except for the confusing INV-id values.

Currently a portal in the invoice table shows the related line items, and in each portal row is the INV_id field. The user chooses the item to be purchased from a popup menu which is populated by a value list.

I want that value list to be populated from the FriendlyName field of the inventory table, and not the INV_id field, but still insert the INV_id into the line item table.

[zincb]

In the Inventory file, create a calculated field : Trim ( Inventory Name ) & " " & Trim ( Inventory ID ) and create a Value list that uses this field. Display this list to the user, in the other file.

In the local file, create a text field (we'll call it "Combined") and create a relationship between it and the calculated field you just created in the Inventory file.

When the user selects from a dropdown list attached to the Combined file, have the Inventory ID get looked up.

[RalphL]

If you have a friendly name that is unique use it as the second field when you define your value list. When you select from the value list the INV-id will be entered. But you said the friendly name wasn't unique. I was trying to givve you another method.

[wplate]

RalphL said:

If you have a friendly name that is unique use it as the second field when you define your value list. When you select from the value list the INV-id will be entered. But you said the friendly name wasn't unique. I was trying to givve you another method.

And I appreciate it, I just don't fully understand. I am a real FMPro hack, I know just enough to sometimes get things done, and enough to kill things bad. I wear many hats, I don't have enough time to know FMPro as much as I'd like.

[wplate]

I'll give this a try, thank you very much.

[wplate]

zincb said:

In the Inventory file, create a calculated field : Trim ( Inventory Name ) & " " & Trim ( Inventory ID ) and create a Value list that uses this field. Display this list to the user, in the other file.

In the local file, create a text field (we'll call it "Combined") and create a relationship between it and the calculated field you just created in the Inventory file.

When the user selects from a dropdown list attached to the Combined file, have the Inventory ID get looked up.

This will achieve what I want, I believe, with the popup menu displying "FriendlyName + INV_id", right?

I didn't realize this was going to be so tricky! :-)

[wplate]

RalphL said:

I am suggesting a portal that shows the inventory. This might be on another layout. The script does the work of puting the part number into your invoice in the correct portal row.

I have been re-reading this over and over to try to understand it, I think I do now. Are you suggesting that there be a layout where I "Select Items for this Order"? The items in the layout are shown from the inventory table via a portal, and there is some Add to Order button that runs a script to add the appropriate bits to the line item?

[lavaface]

I am a new user of FMP myself, but let me take a stab at this. I have a similar issue I'm working on. Essentially, define a new Value List. Use values from a field. For the first field, choose the INV_ID. This will create a list of all of your inventory ID numbers. To make it human readable, click the checkbox "also display values from another field." For the second set of values choose the correponding item name. When you create a pulldown menu from this value list, it will be formatted like "02 Tomatoes; 03 Melons; etc. (except, of course reading down, like a pulldown list. When the user makes a selection, only the number code will be entered into the field. Does this help?

[wplate]

Thanks lavaface. This would be a good idea if our INV_ID were so simple as 02 and 03. But-- Maybe they should be! I'll consider that.

[lavaface]

wplate:

FWIW, we have a large number of items that must be barcoded. I have broken everything down into 2-digit "categories." The first two digits are the category_id, the next 2 are the model_id, the next two are the kit# (we have several bundles of identical video kits) the last 2 are an open code. Perhaps it would be beneficial to represent these items with a similiar ID system. You could still have odd INV_IDs for human usage, just relate them to a SYS_ID that you use for internal database purposes.

Cheers!