13 Replies Latest reply on Jul 7, 2011 8:27 AM by Terri

    Short Term Relationship Issue

    Terri

      Title

      Short Term Relationship Issue

      Post

      I know this is an issue of primary key, foreign key, but I am hoping I can find a short-term solution to my problem while we are in the process of redoing on invoicing. Here is the issue right now:

      1. We have a main job - Job 1 (invoice number).

      2. Sometimes we have to duplicate that job for another customer - Job 2 (invoice number).

      3. Job 1 already has all the cost associated with it, and Job 2 is only a fraction of that. So I want the cost fields for Job 2 to be a calculation that looks up the cost of Job 1 and then computes the cost for Job 2. For example, job 2 is 25% of job 1.

      How can I create a script or a calculation that says look up the cost of Job 1 (and perhaps this starts with a dialog box or a drop down box or find function or something like that), then my calculation takes that figure and performs a calculation to figure the cost of Job 2. Is this even possible?

        • 1. Re: Short Term Relationship Issue
          philmodjunk

          Does this ever extend to 3 or more such records with the same Invoice Number?

          It's possible to link your table to itself by invoice number and this can enable a calculation to connect to a previous record with the same invoice number.

          JobsTable::InvoiceNumber = JobsSameInvoice::InvoiceNumber (JobsSameInvoice is a new table occurrence of JobsTable.)

          If this never extends to a third job with the same invoice, this expression would include the invoice amount from the previous invoice if it exists:

          If ( Count ( JobsSameInvoice::InvoiceNumber ) > 2 ; JobsSameInvoiceNumber::InvoiceAmount + InvoiceAmount )

          • 2. Re: Short Term Relationship Issue
            Terri

            Althought I can't think of where this would affect three records, who knows what the future holds.

            The issue I have with your solution is these are not the same invoice number. Each has a unique invoice number (so rather than job 1 and job 2 I should have explained it has invoice 1 and invoice 2). But invoice 2 has a cost that is a fraction of invoice 1. The question is how can I make this a caculation by indicating somehow that invoice 2 should go find the cost of invoice 1 and then multiply that by say 25%.

            Does that make more sense? Trust me, in the future I hope to use a common invoice number with a unique job name. That way I can use your relationship suggestion. But right now I have to back fill these calculations for these separate invoices and I really don't want to do it by hand.

            • 3. Re: Short Term Relationship Issue
              philmodjunk

              Yes, but in the future, you should modify your design to avoid this issue here.

              I don't see from your posts any way to correctly identify that a given record is "job 2" so that we can find "job 1". How do you tell that two such records should be linked in this fashion?

              • 4. Re: Short Term Relationship Issue
                Terri

                First on my list for 2012, trust me!!! I came into this database late in the process so I'm working with what I have for now.

                I wasn't sure the best way to link these two records. But I think I will have one field that is the current invoice number (job 2) and then a field on that same record that is called main invoice number (which I will input job 1).

                • 5. Re: Short Term Relationship Issue
                  philmodjunk

                  That's the mechanics of one way to link the records,  a join table of records to link them might be even better, but it's not really the answer to my question. If You, the user are flipping through these records, what tells you that a given pair of records should be linked. (How do you know to link a particular pair of records in the first place?)

                  • 6. Re: Short Term Relationship Issue
                    Terri

                    Sorry, misunderstood your question. I know from a report I get from another department, nothing within the database. So the linking would be a manual operation in the first place.

                    If a joint relationship will work better, I'll take advice on that solution. Whatever you suggest.

                    • 7. Re: Short Term Relationship Issue
                      philmodjunk

                      Don't really know as I don't know what information in common identifies the records. Apparently nothing within the actual record.

                      If you only need to refer to one previous record, the method you described should work just fine.

                      • 8. Re: Short Term Relationship Issue
                        Terri

                        We can abort the joint relationship right now and I'll tackle that for my 2012 rework of the current database.

                        So what method did I describe that should work fine? Back to my original question, how do I tell FM to go find the data in that particular field in Job 1 and then take that times 25% and enter it for Job 2? I'm assuming it is a combination of script and calculation. Also, I think it should include an IF function since not all records or jobs will have this issue. For example job 3 and 4 and 5 are just straight jobs with their own cost associated. Then we have job 6 that is again a "remake" of say job 3 and needs to go find that cost from job 3 and perform a calculation and enter that as the cost of job 6. So I will have two more fields on my layout (in addition to name of job, customer, dates, etc.). One will be a dropdown box that indicates remake, then another field that says remake of what job.

                        • 9. Re: Short Term Relationship Issue
                          philmodjunk

                          "I think I will have one field that is the current invoice number (job 2) and then a field on that same record that is called main invoice number (which I will input job 1"

                          Entering the Invoice number of the previous linked job will enable you to define a relationship that links your current record to this previous record.

                          Jobs::MainInvoiceNumber = PrevJobs::CurrentInvoiceNumber

                          • 10. Re: Short Term Relationship Issue
                            Terri

                            Unfortunately there is still a disconnect and something is not clicking. Understand what you are saying but can't seem to put it into practice. I know you have tried to explain this to me before and I apologize that the light isn't going on. I'm in the process of creating a mock database that you can see how I have it set up and then help me. Thanks for your patience. I'll be back.

                            • 11. Re: Short Term Relationship Issue
                              Terri

                              Phil, can we start over at the beginning rather than my re-creating a database to show you? My 4shared subscription has expired and I don't want to create another one if I don't have to. I understand your premise about the relationship solution, but I still don't understand how the calculations will work.

                              I have a layout that has the following fields (a very simplistic view):

                              Job Name - entered value

                              Invoice Number - entered value - assigned from another department

                              Reference Invoice Number - entered previous invoice number - information given from the cost department

                              Cost 1

                              Cost 2

                              Cost 3

                              Cost 4

                              I have 10 jobs with unique names and unique invoice numbers (assigned by another department so they are not necessarily invoice 1, 2, 3, 4, etc.). Let's say Job Nine has invoice number 99124. It actually needs to use Job Five (invoice number 876732) cost information as a basis for its cost information (Cost 1, 2, 3, 4 for Job Nine is 25% of Job Five).

                              How do I connect the two?

                              • 12. Re: Short Term Relationship Issue
                                philmodjunk

                                Instead of separate fields for cost 1, cost 2, cost 3, etc. This should be a table of related records you can access via a portal from this layout. You can add that change to your list though you don't have to wait until 2012 to do so. Once you have the related table of records in place, you can use Import records to move the cost data from your current table into the new related table.

                                You haven't indicated how one invoice will "use" the data from another. That limits the degree of detail I can use in this example.

                                Open Manage | Database | Relationships

                                Select your table for this data by clicking it.

                                Click the button with two green plus signs to make a new table occurrence of it.

                                Double click this new occurrence to change the name to PreviousInvoice

                                Drag from one occurrence to the other to produce this relationship:

                                Invoices::Invoice Number = PreviousInvoice::Reference Invoice Number

                                Now you can define calculation fields in Invoices that refer to fields in PreviousInvoice. If you want to compute the total cost of the invoices combined, you could use this expression:

                                Sum ( cost1 ; cost2 ; cost3 ; cost4 ) + sum ( PreviousInvoice::cost1 ; PreviousInvoice::cost2 ; PreviousInovice::cost3 ; PreviousInvoice::Cost4 )

                                • 13. Re: Short Term Relationship Issue
                                  Terri

                                  Testing...