LiveCode Forums.

Hi,
I use LibDB for my application.
I have a customer table which holds values customerID, customerName etc.

In my "Sales" table, I have a column "customerID" (integer) which I use to store the customerID of the chosen customer. In the "Sales" form, I have created a combobox which I use to display the value of the "customerName" column. When saving the information, I want to save the customerID NOT the customerName. How do I retrieve the corresponding "customerID" when all I have populated the combobox with is "customerName" values?

I hope this makes sense.

Hi nicoloose,
Welcome to the forum

Your question is a bit odd because you say you have a table with id/name in it. So it should be easy to look up the id associated with the name? Since you say you are using a combobox there must not be that many names, so just using if/then or switch/case will do it.
Yes, there will be problems if you have 2 customers with the same name.
Is there a reason if/then wont work for you?

Simon

Hi Simon,

Well I do actually have customers with the same name which is why I needed the name column to be unique. So when I populate the combobox, I wondered if there was a way to store the ID in the combobox but hide it.. It seems crazy to do a lookup to populate a combobox and then another lookup to store the ID into another table?

Cheers
Nic

Hi Nic,
I'm sure you have your reasons but using a combobox with 2 choices in it like:
John Smith
John Smith
How would I know which one to pick?

There is the situation where the names are the same but they include an id:
Home Depot #606
Home Depot #609
Indicating a particular store, I still have to know the id to select the correct one.
Maybe you should just include the id?

Simon

Hi Simon,

Yes, you make a valid point. I think in terms of cleanliness, I wanted to store the ID as opposed to the name field in the sales, production and deliveries tables. My current table structures look something like this:

Customer Table: Product Table
customerID productID
customerName productName

Sales Table:
salesID
invoiceNo
customerID (Perform a lookup on the customer name but only store the ID)
productID (Perform a lookup on the product name but only store the ID)
qtySold

Production Table:
productionID
salesID (Perform a lookup on the invoiceNo but only store the salesID)
qtyProduced

And so on.... I just thought there was a simple way of creating a lookup field which would use one field as a display field but actually use another to store in the record.

Regards
Nic

Hi Simon,

This is the solution that I have come across... When opening the card, I get the customer and product tables and put them into global arrays.. I populate comboboxes and then in each combobox, I do the following:

global myCustArray
on mouseUp
put the menuHistory of me into tKey
put myCustArray[tKey]["customerID"] into field "customerID"
end mouseUp

and then I save the customerID into the table as opposed to the customerName.

Thanks for your help and suggestions. I guess I needed to just sleep on it last night before finding a way..
Nic

Wicked Cool!
Glad you made it work for you.

Simon

Hi nicoloose,
The way I do this is to select the id and the name from the db, then have a repeat loop that splits the two columns into two separate lists. Set the text of the combobox to the list containing the name and set a custom property of the combobox to the list of ids. You could also have two separate SELECT statements instead of having to split the results of one SELECT into two lists.

WHen the user picks an item form the combobox, the Livecode menuHistory property will contain the line number that was clicked so you can use menuHistory as the line number of the custom property holding the id corresponding to the name picked by the user.

Pete