Nested portal issue

[NeyRam]

Hello there! I am working on a database as follows:

- DB structure is ParentTable => ChildTable => GrandchildTable.

- ChildTable is a portal in ParentTable with 4 fields: IDMatchField, CostCode, Description and Total.

- GrandchildTable is also a portal related to ParentTable via GlobalField and a SetField script trigger on RecordLoad, and it has records: PrimaryKey LineItemDescription, UM, Qty, Amount and AmountSum (a summary field).

- ChildTable::Total can be entered either manually or via calculation = GrandchildTable::AmountSum, for those ChildTable records that need to be broken down in GrandChildTable.

Let's say I enter my first record in my ChildTable portal, and the Total field populates after I enter two records in the GrandChildTable portal. The problem I have is that when I enter my second record in the ChildTable portal, the GrandChild table portal doesn't reset to zero record and keeps showing the breakdown (records) I entered for the first ChildTable record.

Can someone shed some light here? Thank you!

[comment]

I don't understand your description. A screenshot of the relationships graph (or even better, an example file) could be helpful.

 

[NeyRam]

Hello again. Please see the attached file. The way the relationship should work is that for every item in the estimate data portal, you can have one or more line items under Cost Code Breakdown, or none at all (manual entry). I'm having trouble getting it to work. Please let me know. Thanks,

 

N

Database.fmp12

[Ocean West]

 

[NeyRam]

Hello. Was the bug issue resolved?

[Ocean West]

Yes

[NeyRam]

Awesome! I’d appreciate your help with the question (and attached file) I posted above. Thank you.

[comment]

I am still not sure what exactly you are trying to accomplish here. I think I understand the Estimates -< EstimateData >- CostCodes part. This part is not implemented correctly in your example file. The EstimateData table needs to have a field to act as the foreign key to the Estimates table, and this should be the match field in the relationship between the two. Also, there should not be a CostCodeDescription field in the EstimateData table. Everything describing a cost code should be in the CostCodes table. The EstimateData table should contain only the foreign key to the CostCodes table.

I do not understand the role of the CostCodeBreakdown table. If you want to see a breakdown of the estimate's items by their cost code, then this would be done using the records in the EstimateData table themselves, without the need for an additional table. The easy way to do this is by producing a report from the EstimateData table, sorted and grouped by the CostCode field. If you want to see this in a separate portal then it gets a little more complicated - but it would still use the already existing records in the EstimateData table.

If I misunderstood and it's something else you want then please clarify and explain in detail.

 

[NeyRam]

Good morning and thanks for your reply. Here are my answers:

1. Regarding Estimates -< EstimateData >- CostCodes, I could create a CostCodes portal to show records from CostCodes but the problem is that the field CostCodeTotal does not belong to the CostCodes table. Values for this field are either manually entered or calculated from the CostCostBreakdown items.
2. As to the role of the CostCodeBreakdown table, each cost code will appear in the EstimateData table only once. For example, if you enter a cost code for supervision costs for a project, you will only enter it once since you will not charge twice for supervision. It's not that I will make a search to pick all the records with the same cost code. Each cost code only appears once in that table.
3. What I want to achieve is the following: a) I enter/select a cost code in the EstimateData portal (let's use Supervision to continue with the same example) b) if supervision costs are a lump sum number, then I will manually enter the amount in the CostCodeTotal field. If supervision costs have different components that need to be broken down, then c) I will enter the breakdown in the CostCodeBreakdown table, i.e. Project Manager, Superintendent, etc., with their respective calculations (UM, Qty, Price, Amount), and the CostCodeTotal field in the EstimateData table should summarize all the entries in the CostCodeBreakdown for the Supervision cost code. Then I move on and select another cost code, and the operation repeats.

Please let me know if I was able to clarify this a bit. Thank you.

N

[comment]

If I understand this correctly, you want to have a basic structure of:

with a calculation field in the EstimateData table to add up the "local" cost (entered in the EstimateData record) together with the costs entered in the child records in CostCodeBreakdown.

Please note that the relationship between EstimateData and CostCodeBreakdown needs to be based on matching an EstimateDataID value (the primary key value of the EstimateData table), not the CostCode value (or at least not the CostCode value alone; it could be based on both EstimateID and CostCode).
 

Then you want to have a 2nd occurrence of the CostCodeBreakdown table linked directly to the Estimates table, matching on a global EstimateDataID field defined in the Estimates table. This would be the TO to use for your 2nd portal, showing the breakdown for the currently selected EstimateData item.

---
P.S. Consider simplifying things by entering everything into the EstimateData table (even if it means having duplicate CostCode values there) and then just summarizing the items by their CostCode value, as I explained in my previous post.

 

Edited July 30 by comment