What is the best method to set up a customer ordered from address and ship address
So my logic is as follows the following
Customer Table/form - contains Account Id and Customer Name (plus other fields)
Branch Table/Form - contains Account Id and Branch ID plus company name and address fields
Relationships set to ensure look happens when both account and branch id input
Order table - Has the relevant fields on the form, for the order from name and address this is easy
Enter account id/branch id (relationship)
And name and address fields have lookup branch customers etc. This works okay for the ordered from bit
But when it comes to setting the deliver to fields
I need to look up another related branch but cant get this to work.
Ie ordered from - account IAN501 branch 1 = lookup the Birmingham address
Ie deliver to - account IAN501 branch 2 = lookup the London address
I figured If I add another account code (ie account2) the this could default to same as account 1 if order and deliver address are the same
But if not the same then I could get a derivation to work some way but cant get the syntax to work
Ie along the lines of if account = ian501 then lookup details else look up the branch details of account 2
Or can anyone suggest a much better approach to this?