Filemaker relationships

I have tables called "estimates", "line items", and "items".  Estimates have line items which are displayed through a portal on the estimate layout, each line item is an item used in that particular estimate.  

I would like to be able to bundle some items together into an "assembly" and have those items related to the assembly be displayed through the same portal as the line items.

So individual items, and all items related to an assembly would be displayed through the same portal, all of which are related to the estimate.

I can't figure out how to do this.  It doesn't seem to fit a scenario where I would use a join table or a self join.

Can anyone provide some guidance?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresidentCommented:
There are a couple of ways to do it (at least). One way to approach it would be to add an "Assembly field" to the Items table. If you populate the Assembly field for a group of Item records then they the Line Items on the Estimate can be sorted by Assembly and will appear grouped, especially if you include the Assembly name field in the Estimate.

There are also various ways to make the Assembly Items to appear a bit different , using indents etc, by way of calculation fields for displaying the Item Info on the Estimate. So, for example, if an item is part of Assembly (because the assembly name field is not empty), the the calculation field for the name of the Item includes the Assembly name and perhaps slightly indents all Assembly items after the first one so they appear grouped. You can determine the "first" item in the assembly either by using a sort Order field and manually assigning the order or by a Self-Join relationship within the Items table based on the Assembly name.
Quick question - which version of FMP are you using?

For traditional FileMaker, I agree with willmcn's first suggestion - start with the items table and work from there (you're best to build your report based on the table of the lowest common denominator.

Create calculation fields for the "parent records" of the 2 tables above and link them all together with IDs so each item should have the chain - Item > Line Item > Estimate.
Ideally you want to have a separate calculation field for the information you want to show from each related table (so you can display different types of items in the portal.

If you wanted to have separate header rows to show your assembly, you'll need to create those records too in your items table, and give them a flag or something to denote it's a header row (and what type: LineItem or Estimate). You can use conditional formatting to color these differently in your portal.


If you're using 12 (or open to using a plugin) another option is to use a "VirtualList".
Basically you gather all your data into a "delimited text field" and then use a table with
With the advent of SQL calls in FileMaker, you can also now do a SQL call and get all the data you need from the different tables, then display it all as a "virtual list" that parses that text field. Explaining how a virtual list is perhaps beyond the scope here but I'd suggest reading up on it - it's an amazing tool for reports like this (and you can do far more with pulling data with SQL calls than you can with traditional FileMaker relationships.
Here's a starting point:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sstiltonAuthor Commented:
I guess I didn't explain very well what I was needing, but the answers here helped with a different question.  I was able to solve both problems.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.