• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 615
  • Last Modified:

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?

2 Solutions
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: http://www.mightydata.com/blog/virtual-list-in-three-easy-steps/
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now