Jump to content

A relationship joining two match fields


Dr Nevs
 Share

This topic is 1791 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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!

 


 

 

 

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

This topic is 1791 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.