Forum Discussion

AndrewS's avatar
AndrewS
Qrew Trainee
3 years ago

Is it possible to use not PK field to establish one to many connection?

Hi,

I have:

'Budgets' table which has 'Customer', 'Estimate'(PK) and 'Amount' fields,
'Budget lines' which has 'Estimate'(FK) and 'Amount' fields,
'Invoices' which has 'Customer' and 'Amount' fields.

'Budgets' have many 'Budget lines' (connected thru the "Estimate")
I would like to connect 'Budgets' and 'Invoices' using 'Customer' field, but my primary key in the 'Budgets' table is 'Estimate'

Is it any way to use 'Estimate' field as a PK for 'Budgets' - 'Budget Lines' connection
and at the same time
use 'Customer' field as a PK for 'Budgets' - 'Invoices' connection.

Thanks in advance.

------------------------------
Andrew S
------------------------------
Relationships
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    3 years ago
    I think you would have to look into using a pipeline to relate the invoices. When 'Customer' is changed in the Budgets table, search Invoice table for that same customer, and set the Related Budget field.

    Though, this goes down a dangerous path as if you change your estimate you will lose the children (and will need another pipeline to go find any children and keep them related). Careful changing primary keys...it can get messy.

    ------------------------------
    Michael Tamoush
    ------------------------------