Share information between records
Attached you will see a pdf to show a rough sketch of my layout (it doesn't show the entire layout, to the right are just more calculation fields). I'll try to explain my question.
1. Working with FMPro 11 Advanced
2. All fields are part of the same table. The fields in color are all calculations based on some of the edit fields in the record. So right now I have one table with no relationship (don't cringe, I didn't create this monster.)
As you can see I have two records: They have unique job names and invoice numbers. In this example Book Name One (with invoice Test 1) is the main job. It shows all the costs associated with that job (which are the calculations mentioned above). Book Name Two (with invoice Test 2) is a "remake" of the main job. The costs for this job are not the same calculations as the main job. They actually need to use the cost from the main job and then figure a percentage of that. So in this example, the Cover Run calculates to $10,312 for the first record. I need the Cover Run for the second record to be 60% of this $10,312. (To make it easier, it is always 60%.)
I know I need some sort of relationship between these two jobs. That is why I put the Main Job field in the layout. My calculation needs to include that if this field is populated, that is the Invoice # to use for the calculation. I know how to create a one-to-one relationship within this table, but I'm not sure how that works or if it is part of the solution for the problem. (PhilModJunk: if you are reading this, I know you have tried repeatedly to explain this to me, but I need to start from scratch.)
Any ideas? Or is this beyond the scope of this simplistic table and/or FileMaker in general?