Column Specific Lookup Model Driven App

[matt1405]

Hi everyone, 

I'm building a model driven app and I'm trying to produce a lookup column "customer number" in a invoice table, to look up against an equivalent text column also "Customer Number" in the Job table.

When I make the Lookup Column "Customer Number" it looks up the primary name column in the job table "Job Number". 

How can I get it to lookup against the "Customer Number" Column instead.  

[dpoggemann]

Hi @matt1405 ,

Lookup column will always reference the primary name column in the referenced table so if that column is the Job Number as the primary name column on that table, this is what will be used.  You can not create a lookup relationship the way you are trying to do in a model power app.  I would suggest doing the following:

1.  Create a Customer Table (or utilize the Account table since this already exists)

2.  Add this as a lookup column to your Invoice Table and as a lookup on your Job table

Hope this helps. Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

[matt1405]

Thanks @dpoggemann 

I'm a bit confused. 

If the field I want to read is a text field in a job form called "Customer Number" and not the Primary name column, will I not have the same issue  looking up from a new "Customer Table"? 

[dpoggemann]

Hi @matt1405 ,

What I am saying is that is not possible.  You need to utilize the "primary name field" on the table to create relationships.  My thought was the following:

1.  Table for Customers that will have a unique Name column of course and you can have additional fields that describe the Customer on this table as well including customer number, address, and whatever else you want.  This is basically what I use the Account table for most of the time.

2.  Job table will be related in a N:1 to the Customer table so it will have a lookup column for the Customer.  Note, if you have this you can add the customer number from the customer table to a view on the Job table from the related tables.

3.  Invoice table would be the same as #2 with the N:1 to the Customer table.  

Overall this does not create a relationship directly on the Job and the Invoice but I don't think that is what you are trying to do here right?

Hope this helps.  Please accept if answers your question or Like if helps in any way.

Thanks,

Drew