use value list in calculation

[David Nelson]

I have a value list which is the id of customers and it shows second value of their company name.  This all works fine and I sometimes put the value list on a layout as reference showing the name only as pop-up except I turn off entry.  This works well in that a relationship is not needed to the parent table from companies.

 

My problem is this

 

I have a table of SALES and in it is the company id and a sales rep id but nothing else in the sales table names it identifiably unique except the date.  So I want to display in its second description for the value list, the company name and sales rep name and date.  However, all that is IN the Sales table are the company id and sales rep id and I need their names instead.

 

For it to work in a value list, they cannot be unindexed so relating them and including the company name through the relationship doesn't work so I am stuck.  I know we are not supposed to put names as text and instead use ids but how can I handle this please?

[comment]

I am afraid this is rather confusing:

 

I have a value list which is the id of customers and it shows second value of their company name.

 

I have a table of SALES and in it is the company id and a sales rep id but nothing else in the sales table names it identifiably unique except the date.  So I want to display in its second description for the value list, the company name and sales rep name and date.

 

1. I presume this is another value list being discussed here?

 

2. What is "company id"? Is the same thing as "the id of customers" in the previous quote?

 

3. What if a sales rep makes two sales to the same company on the same day?

 

Note that the "second field" in a value list needs to be indexed only if you wish to hide the "first field". In such case you would need to lookup the names into their own fields (or auto-enter the calculated concatenation into a text field), if you want to adhere to the described scheme.

[David Nelson]

Sorry I was not clear.

 

1. yes, there is a table of Customers which has a field which holds the company name.

2. yes, I meant Customer ID. I recently changed the table's name and I have confused myself.

3. if sales rep makes two sales to same company on same day then it will show two entries in the value list and you ask good question.  I do not know how else to show a record for selection in the value list because it seems that sometimes it might take many fields to make it clear.

 

and it makes me think maybe if it takes that much information to make it clear then maybe it is the wrong method.  In sales orders there would not be two by same rep on same day.  I would not mind showing the id except that it is that new UUID and it is gross.  If I lookup and the company name is changed in Customers, how would I fix the sales order?

 

Thank you for taking a look at this problem.

[comment]

I do not know how else to show a record for selection in the value list because it seems that sometimes it might take many fields to make it clear.

 

Perhaps you should consider other methods of selection, such as a portal or a new window showing records in list view.

 

 

Your mention of "that new UUID" makes me suspect you are not using version 11 as your profile indicates? If so, you may have even more possibilities here with popovers.

 

 

If I lookup and the company name is changed in Customers, how would I fix the sales order?

 

You would need to find the related records and perform a relookup:

http://www.filemaker.com/help/13/fmp/en/html/scripts_ref1.36.60.html#1029621

[David Nelson]

Thank you, we are in 11 at present but I am making changes in 13 for moving up maybe even to 14.  I wondered before whether to change my display version in profile.  I should have mentioned in the post then that it was 13.

 

A relookup seems like it would work but all scripts can fail I have read in more ways that record locking not that I need to tell you.  It seems better to focus on different selection situation and display.  I forgot about popovers and I could use portal on it so I can display the names of company and rep in popups like before.  It does not need to be in a value list combining company and rep and date.

 

As always, I am grateful for your advice.

[Lee Smith]

Version 14? There is no such version.

 

If you are developing in version 11 than your profile is correct. However, if you are asking about a different version then, make that the first line of your question.

 

i.e. Using version xx.x on a Mac/Win .... How do I.....