Match fields by friendly name

[Chris Pink]

Apologies for this newbie question, it seems like it should be obvious, but....

Database, two fields Customers and Jobs, relationship by Customer ID. 

When a new Job is created you need to choose a customer, I want to choose the Customer by Customer Name but have the relationship by Customer ID - ie choosing the Customer only depends on knowing the Customer Name, not the Customer ID

Or do I need to make the relationship on Customer Name to be able to choose Customer Name in the Jobs layout?

[comment]

If you are choosing by selecting from a value list using a drop-down list or a pop-up menu, you can define the value list  to Also display values from second field. Make CustomerName the second field and check the option to Show values only from second field. 

Note that the behavior of field formatted as a drop-down list will be different from a field formatted as a pop-up menu: after exiting the field, the drop-down will display the selected CustomerID, while the  pop-up will continue to show the name. But the field contents  will be always the ID.

And of course you must have unique customer names for this to work.

[Chris Pink]

Thanks for the reply

Something about my understanding is skewed here. 

I have done what you suggest, thank you, and yes, it does the look up fine.

But it won't show Customers in the list until a Customer is assigned (the dropdown or menu is blank). Then, if I try to change the customer by name it is trying change the Customer ID for that Customer Name (ie I get the "unique values" message) I get this even if the control is an Edit Box. To clarify; this is within a layout based on Jobs

I have worked out that what I need to do is the other way around, ie create the Job within the Customer layout (through a Portal) rather than assign a Customer within the Jobs layout after creating a job.

But I don't understand this logic. I don't see why I can't make a Job then assign a Client

My relationship is set one-to-many Clients::Client ID -> Jobs::Client ID 
Creation of Jobs is allowed through this relationship but Creation of Clients is not (Otherwise many clients with the same name are created.

 

Edited July 18, 2019 by Chris Pink
Clarification

[comment]

You are obviously doing something wrong - but I cannot tell from your description what it is. See if the attached demo helps.

SelectParent.fmp12

[Chris Pink]

You're right, I was and thank you. I had the Client ID field from Clients whereas your example has the Parent ID from Children (of course, they cry). A numpty error.

Thanks very much for taking the time to help.