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!

 

 

 

 

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.

 

 

Ah! yes, interesting.

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

Thanks again

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