Lookup fields through join tables. How?

[Matt@action]

Hi,

I am having a fairly fundamental problem with getting a lookup field working between an invoice and a job table.

The job table is connected via join table to the invoice one, so that one job can have many invoices.

I have a PO number field that I wish to hold one number and at the point of invoice creation, I wish it to be looked up into the the invoice field lookup. I have created the fields and all looks well but data is not being copied from one field to another, when I select the field and request relookup data from the records menu.

I have uploaded a Jing video of the problem to explain this in further detail. Please click on the following link to view:

Lookup problem video

Does anyone have any ideas on this?

Is the join table causing a problem, or does the relationship need to include these fields. The fact it asks me if I want to update 4 records would suggest that it doesn't to me, but it is still not working. When selecting the 'if not found use...' option I get it to insert that, so it is obviously missing some finer matching.

Any help greatly appreciated.

Thanks

Matt

[comment]

The job table is connected via join table to the invoice one, so that one job can have many invoices.

Are you sure about this? One job can have many invoices, sure - but can one invoice be for many jobs? If yes, will ALL these jobs have the same PO number?

[Matt@action]

Are you sure about this? One job can have many invoices, sure - but can one invoice be for many jobs? If yes, will ALL these jobs have the same PO number?

Hi,

Thanks for your reply. The invoice table is connected to the job table via a job & customer join table. The Job customer join table was created to allow the One job has many customers relationship to allow multiple interested parties to be joined to the job.

The invoice table is connected via the same join table to the job records so that when creating and selecting invoices from a job, it selects only those customer records already related to the job. Does that make sense?

Is this necessary or is this best achieved by a value list anyway? I have attached a clone file for reference.

I added an additional link from the PO-lookup field on the invoice table to the PO field on the job table to try and avoid the join table but this isnot possible. What are my options here?

Confused and appreciative.

MattARL Database_trial Clone.zip

[comment]

The invoice table is connected via the same join table to the job records so that when creating and selecting invoices from a job, it selects only those customer records already related to the job. Does that make sense?

I don't know. It is very hard to read your relationships, but I think your description is incorrect. You have:

Job -< Join_job_customer >- Customer

which makes sense if a job can have more than one customer and vice-versa, e.g.:

Job A customer: Adam

Job B customers: Adam and Betty

Job C customers: Betty and Cecil

Next, you have:

Job -< Invoice >- Customer

This makes sense only if you issue separate invoices to the job's customers; using the example above, you could issue:

Invoice 001 to Adam for Job A;

Invoice 002 to Adam for Job B;

Invoice 003 to Betty for Job B;

Invoice 004 to Betty for Job C;

Invoice 005 to Cecil for Job C.

Even then it would be better, IMHO, to make Invoices a direct child of Join_job_customer, instead of duplicating the join.

[Matt@action]

I don't know. It is very hard to read your relationships, but I think your description is incorrect. You have:

Job -< Join_job_customer >- Customer

which makes sense if a job can have more than one customer and vice-versa, e.g.:

Job A customer: Adam

Job B customers: Adam and Betty

Job C customers: Betty and Cecil

This is correct. A job can a number of different customers such as an insurance company, site manager, and resident. All may receive different invoices pertaining to the same job.

Next, you have:

Job -< Invoice >- Customer

This makes sense only if you issue separate invoices to the job's customers; using the example above, you could issue:

Invoice 001 to Adam for Job A;

Invoice 002 to Adam for Job B;

Invoice 003 to Betty for Job B;

Invoice 004 to Betty for Job C;

Invoice 005 to Cecil for Job C.

Even then it would be better, IMHO, to make Invoices a direct child of Join_job_customer, instead of duplicating the join.

Separate invoices are sent to the jobs individual customers. Providing the customer record is hooked up to the job record via the join table, an invoice can be raised to that customer.

I am thinking I may need to script some form of copy and paste at the time of the invoice creation. Should I have set the invoice relationship up differently to have made use of a lookup relationship?

Thanks in advance.

Matt

[comment]

I think your graph should look something like the attached. When creating a new invoice, you'd select the JobID first - then use a value list defined to show values from Customers 2::CustomerID, show related values only, starting from Invoices, to pick the customer.