but later found out that ValueCount only counts the number of items in a checkbox set and not a drop down value list.
Value count counts the values in any list of values that you pass to it, this can indeed be all the values in a value list, if you use valuecount with the valuelistItems function--but you don't need that option here:
Use this expression for the auto-enter calculation:
If ( Count ( Contacts::ContactID ) = 1 ; ContactID )
You can specify any field from the related contacts table as long as it is a field that is never empty.
OK I tried that but it is still not working.
Please see attached screenshot of the calculation. Not sure what I'm missing...
Check this post for similar idea. http://forums.filemaker.com/posts/611472e764
"didn't work" doesn't tell us HOW it failed--a key detail that can help us to help you.
Also, adding or changing an auto-enter calculation doesn't affect the value in an existing record, only in a new record that you create, so make sure you test this by creating a new record.
Your right I should have explained better. I did find a way to do it: not sure if it is elegant, but it works.
As I couldn't use the auto enter calc, since the user could be changing or adding additional project contact types on the fly and not just when the record is first created, I had to find a way to bring the data over from the primary calculation field that was referencing the related table to the text field that had the drop down list, in case there were more than one project contact assigned the type of "ordered by".
What I did was apply a script trigger to the the project contact type field, so that when the user modified it a script ran to commit the record and then go to the calculation field. The calculation field also had a script trigger that fires when entered. This script checked first to see if the calculation field was not empty, and if it wasn't, it set a variable, and then set the text field with that name that was in it. If it was empty it would go to the text field and clear it. I did the last step to clear any name in the text field if the calculated field was empty, so if the user deleted the project contact it would clear this field to avoid any confusion.
So, now if there are more than one "ordered by" types the user will need to go to the Quote ATTN text field and select from the drop down value list which person they will send the quote to, and if there is only one "ordered by" type it auto fills with that contact's name.
I appreciated you help gurus!
I couldn't use the auto enter calc, since the user could be changing or adding additional project contact types on the fly and not just when the record is first created,
I would think that this could also create issues for you when using your scripted method--which seems more complex than it needs to be here. An auto-enter calc should work to enter the value that is correct at the time the record is first linked to the related table and then a script trigger could update this at any point when data in the related table is modified--but if this is a concern then maybe should not be using any kind of "data copy" method here.