6 Replies Latest reply on Jul 18, 2011 12:49 PM by Terri

    Share information between records



      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?


        • 1. Re: Share information between records

          The issue is how would the two records relate to each other.

          If there was a field called parent jobid then you could use a self relationship and if stament in the calculation to establish the calculation. However since this calculation will now be a related calc that adds some issues.

          the calc would be something like if(Thistableoccurance::Parentjobid ="", (calc based on this record),(calc based on related record))

          You would need a second table occurance and a relationship between ParentID and JobID.


          • 2. Re: Share information between records

            That is exactly what I want. And the Main Job is that parent jobid that you are talking about. It is the related calculation issues that I'm having trouble overcoming.

            Thanks for weighing in.

            • 3. Re: Share information between records

              You would need something in your child jobs that allows you to link the record back to  the main job.

              In  this case we would need a field called Parent Job

              Then in the manage database relationships tab we would add a second table occurance of this table. name it Parent Jobs

              Then we would link the field called Parent Job in the layouts table occurance to the Invoice Number in the Parent Jobs table occurance.

              for each child job you would need to update the Parent Job field with the matching value of Invoice Number to the Parent Job.

              Then you just need to change the Cover Run calculation to add the if condition of the Parent Job field being blank to calculate values from this record or calculate as a Percent of Related values calculation. Not sure if this might cause  some reference problems in the calculation because I havent tried it yet. If it does you may have to do some work around where the calculation isnt as dynamic and the value is actually written to a field in a script. (if using FM11 you could assign the script to various script triggers to ensure it stays current)



              • 4. Re: Share information between records

                Thanks. This is the relationship that someone else explained in a previous post where I didn't do a good job of explaining the situation (the .jpg helped this time). Your further explanation of the calculation helps. It is the "calculate as a percent of related values" part I'm not sure of, or more specifically, the related values part. I'm not sure how to write that into the calculation. It sounds like you are not 100% sure if this will work or not either. If you happen to test it and find a way to make it work, please let me know. Otherwise I appreciate any other input from other posters. Thanks!

                • 5. Re: Share information between records

                  Lets say your  table occurances are Main and Parent and your calc value is total

                  Then your calc would be like this If(Main::Parent_Job = "", Qty * Price, Parent:Total *.8)

                  It apparently works because I set it up exactly like that.

                  Here is a demo



                  • 6. Re: Share information between records

                    It worked!!! I may need help with some complicated calculations in the future, but this did the job for now. Thanks!