A relationship joining two match fields

[Dr Nevs]

Hey guys,

I don't think this one is actually possible, but hopefully someone out there might have figured out a work around

Lets say for example i have 3 tables in bold and their following fields in italics.

Order
-Order Name
-Order ID
 

Product
-Product Name
-Product ID

Product Tags
-Product Tag ID
-Product Tag Name

Order Details
-Order ID Match Field
-Product ID Match Field
-Product Tag ID Match Field
-Product Quantity

 

I would like to have on an Order layout, a list of all Product::Product Name, and their Order Details::Product Quantity. Even if the product quantity is zero. 

What i do not want to have to do is create an Order Detail record for every Product for every Order.  I only want to have an Order Detail record for when an Order Detail::Product Quantity is 1 or more. Otherwise every time a change to the Product table would occur, i would have to script the changes to to be reflected in every Order Detail record.

I have a similar technique used for the other table Product Tags, were i can list all the Product Tag::Product Tag Name records in the Order layout, and using a script have the Product Tag::Product Tag Name clickable, then show up as bold with Order with conditional formatting    -   not IsEmpty ( FilterValues ( List ( Order Details::Product Tag ID Match Field ) ; Order Tag List::Product Tag ID ) )

I can do that too with the Product Name, but when it comes to showing the Order Details::Product Quantity, Thats where it falls down, because i cannot get a relationship between both the Order Details match fields.

I hope that all makes sense. i did my best to make it as clear as possible, but no doubt i am still leaving some vital info out!

Thanks!

 

 

 

 

[comment]

19 minutes ago, Dr Nevs said:

I hope that all makes sense.

Not really, I am mostly guessing here.

 

20 minutes ago, Dr Nevs said:

I would like to have on an Order layout, a list of all Product::Product Name, and their Order Details::Product Quantity. Even if the product quantity is zero.

If you want to have a portal on a layout of the Orders table that would show all products, you will need to define a relationship between Orders and a new occurrence of the Products table (say Products 2), using the x relational operator.

If you want this portal to also show the quantities ordered in the currently viewed Order, you will have to add a global gOrderID field to the Products table, and define another relationship as:

Products 2::ProductID = OrderDetails 2::ProductID
AND
Products 2::gOrderID = OrderDetails 2::OrderID

and add a script trigger to populate the Products::gOrderID field when you load a record in the Orders layout.

 

 

[Dr Nevs]

Ah! yes, interesting.

Thanks, I'll try that technique, and hopefully it doesn't slow record loading too much.

Thanks again

[Dr Nevs]

Works beautifully, and super quick. Thanks again, again!