How do I collate data through multiple Many-Many relationships

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I am currently building an app that would allow quotes to be created, with the quote containing discrete "Systems" which are in turn made up of product bundles (which I call "Components") which are in turn made up of individual "Line Items" (like a cable or a power supply, etc).

I have to use many-many relationships to achieve this because a "Line Item" might be used in more than one "Component" and a "Component" will definitely have more than one "Line Item". This works ok and I can build up "Systems" from "Components" which have "Line Items" and pull through pricing via Summary fields up from the "Line Items" table all the way through to the "Systems"

FYI, the basic structure looks like this

System < System Components > Components < Component Lines > Line Items

There are currently around 300 "Line Items", 30 "Components" and potentially a large number of "Systems"

My initial question is:

Is it possible for me list on a report all the "Line Items" that make up a "System"? I need to be able to do this so that purchasing know what to buy.

I can create a Report Link from "System Components" to "Component Lines" but not from "Systems" to "Component Lines", since there is no field that is common in both tables. 

Any help would be appreciated. It may be that I'm structuring this all wrong. 

Photo of David

David

  • 20 Points

Posted 3 years ago

  • 0
  • 1
There is a technique where you could push a button on a System and the System record would show it's required Line Items.  So it would not be a report, but essentially putting "Focus" on a system have have that focus shine though the relationships to light up the Line items.  Would that work for you?
Photo of David

David

  • 20 Points
Not sure if I understand what you mean but I'm interested in possibilities. Interestingly enough, I tried to do this in Access just for a laugh and Access seems to have no issues with pulling information through from Line Items to Systems through multiple one to many relationships. Not sure why QB is restricted...
System < System Components > Components < Component Lines > Line Items


The basic idea is to have a table with a single record in it to record the Focus System Record ID. That would be a record with Record ID#1.
You would make a URL formula button on the system to populate that field on that record with the record ID of the system that you are on.

Then on the System record make a  formula numeric field with a  formula of 1.

make a relationship back to that Focus system record and lookup the focus record id.

Make a formula field called [System is in Focus (=1)] and make it calculate to 1 if the record ID of the system is in focus.  ie it is equal to the focus system.

Look that field [System is in Focus (=1)]  to the System components

Then do a summary maximum up to the Components

Then look up that summary maximum down to the Component lines

Then look up that summary maximum down to line items.

Great, now all the line items will be flagged if they are used on the focus system

Put a report link field on the system and use a trivial common value like a formula field of 1 on each end to link to all records.  Set the form to use a report of line items which are flagged with that summary maxim field =1.  

Have a form rule to only show that report link field of the system is in focus and yur done! 
Photo of David

David

  • 20 Points
Ok, I'll give that a try this afternoon and see how I get on. Thanks for your quick response!
Photo of David

David

  • 20 Points
Ok,

STAGE 1:

I created a new table called "Focus System" (dbid=bkudhejw3)
I created a new field called "Focus System Record ID" (fid=6)
I created a single record in the table with Record ID# 1
I set the "Focus System Record ID" to 1 initially.

In my "Systems" table I created a new Formula URL field called "Focus" and set the formula to:

var text URLONE = URLRoot() & "db/bkudhejw3?act=API_EditRecord&rid=1&_fid_6=" & [Record ID#] & "&apptoken=xxxxxxxxxxxxxxxxxxxxxxxx";

var text URLTWO = URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [Record ID#];

$URLONE & "&rdr=" &URLEncode($URLTWO)

I made it a button with the Link Text "Tag this system"

I can now click the button and it changes the "Focus System Record ID" to the Record ID of the system.

Moving onto Stage 2...
Photo of David

David

  • 20 Points
STAGE 2:

(BTW I wasn't sure why I needed to:

 "Then on the System record make a  formula numeric field with a  formula of 1"

and also

 "Make a formula field called [System is in Focus (=1)] and make it calculate to 1 if the record ID of the system is in focus.  ie it is equal to the focus system."

 so I just did the second one) UPDATE: I guess the first one was for comparison when I come to creating the lookup field? I'll just use the "System is in Focus" field instead for the moment.

I created a one-to-many relationship between the table "Focus System" and "Systems" (i.e. one Focus System can have many Systems) and created the Lookup field "Focus System Record ID" within "Systems". I also set the default value for the "Related Focus System" to 1. This means when I create new systems they are automatically linked to that single "Focus System" record so that the functionality will work.

I created a Formula - Numeric field called "System is in Focus" with the formula
If([Focus System Record ID]=[Record ID#], 1, 0)

I then looked that field up to "System Components"
I summarised that field to "Components" with max 1
I looked that field up to "Component Lines"
(I don't think I need to go all the way down to Line Items as Component Lines should give me the info I need)

Now I have a 1 next to every record in Component Lines that is used in the focus system (happy days!)

I created a report link field within the "Systems" table that looks up records in "Component Lines" that have the same value as "System is in Focus" (this means that you won't see a different record's Component Lines by mistake, even if viewing from a different form) and also set a Dynamic Form rule to only show the Report Link if "System is in Focus" = 1.

Seems to work fine! Thanks for your help!