I assume 'add time' means labor, and 'work-out' means work subcontracted. I also assume you have a standard relationship like:
__pkEstimate (Estimate Table) =<_fkEstimate (Lines Table) and _fkItems(Lines Table)=>__pkItems(Items Table)
If so, and if it were me, I would make material, labor, and sub contractor all one table, called items. I would add a field to that table called something like "Item Name" which would be a drop down consisting of the categories (Material, Labor, Sub-Contract). I would also add a text field to your portal (Category).
Then on your line item portal you could add line items by calculation or look up from the Items table.
This would also help with finds and reports. Someone may have a better idea.
Thanks for taking the time to reply. This route had crossed my mind, all though I hadn't fleshed it out quite as far as you had. The problems I have though are...
Materials - imported from suppliers CSV file, and has 4 layers of conditional value lists to drill down to the final item.
Time - much simpler, will only require one conditional value list, and equations will be based on time rather than decimal.
Out-Work - Will not be a standard cost. Will be a different cost everytime. Which will be chosen from a quote-request table, and then converted to a purchase order in a purchase order table.
I have all of these working separately perfectly, and can all appear in their own portals. Would just be nice to have them all in one portal.
Then you're method is better. I think what may help you now is if you google "FileMaker Virtual List". You essentially would have a script that would loop thru the line items on all 3 portals, and create a list. Here's a few:
I think I may just stick to the 3 portals. Setting up that script/looping situations, seems overcomplicated, especially for someone who has just started their filemaker journey.
I was hoping there would be a way to have a top level drop down, which would dictate which table you were then going to select from, if you catch my drift.
You could set up several popovers, each with a field, a value list and a script. You select an item from the value list, and the script uses the value selected in the field to add an item to a single unified portal. Each popover could be a value list that draws values from a different table.... These popover can be external to your portal, they need not be placed inside the portal row.
You might also consider a popover with a slide control inside of it and a different value selection portal inside each panel of the slide control. You click to open the portal, click through the slide control panels to find the general category, then use the selection portal to find and select the precise item that you want to add to your LineItems portal.
There are some similar controls demonstrated in "Adventures in FileMaking #2 - Enhanced Value Selection".
Some really clever/interesting stuff there, but I'm very cautious that I don't want to try and run before I can walk, if you know what I mean. And it may be easier to just use the 3 portal system to get something up and running, after all it isn't customer facing so doesn't need to look glamorous.
However I have now stumbled upon another issue. Using quote requests I have added a drop down list to select yes/no on each quote request to select whether they are used as part of the final calculation for the estimate. This works fine, and only the relevant quote requests are displayed in the portal (using a portal filter). However the total (using a total of summary) will display the total of all records related to that estimate, as opposed to just the ones displayed in the portal (or with a yes in the relevant field). Is there a simple way around this? Or do I need to write a complicated calculation to achieve the same result?
Thanks again for all the support
I think once I have this working, a slightly slicker version using some of the techniques in the adventures in filemaker document will be in order.
OK i think I have sussed the portal sum problem, not sure if this is the best way to do it. But I have created an additional field, a calculation, with the if function, to copy the amount based on a yes or no being in the relevant drop down field. Then a summary field of that calculation field. Seems to work so far.
When you filter a portal, calculations and summary fields that evaluate from the context of the parent record ignore any filtering. But if you placed a summary field from the portal table inside its own one row portal with the same portal filter as specified for the original portal, it's summary value will be based solely on the related records that pass through the portal filter.
Lovely, thanks Phil.