3 Replies Latest reply on Jun 7, 2010 9:51 AM by philmodjunk

    Calculated field to equal specific field from specific record?

    jomonster8u

      Title

      Calculated field to equal specific field from specific record?

      Post

      Hi everyone.  I'm really enjoying filemaker and have done some fairly complex stuff but I am totally stumped about this next project.  I think I might need to use variables and expressions but I have no experience with those, and I just can't seem to get it to work.  Here's what I need to do:

       

      I have a table "Reports" and it has numerous fields.  The issue is with the number of the report, which is based on our business project number.  The second part of the report number is sequential beginning with 1.  I'm pretty sure I'm on the right track with making 3 fields: My project number which is assigned as a member of a value list from another file that stores all of my project information, say "Project", my report number, which is the sequential part, so "ReportID", and  "k_ReportNumber", which would be a unique calculated field (Project & "-" & ReportID).

       

      The problem is this:

      Each project will have multiple reports.  How do I calculate the ReportID so that it will check if any reports exist for that project already, and if they don't, set the field as "1", and if they do, get the most recently created report and add one to it so I can do the next one? 

       

      The second problem is a related issue:

      I will also need to copy certain fields from the previous report for that job to different fields in the new/current report.  The issue is that when using GetNthRecord or something like that, the previous or last report may not be for the same job. 

       

       

      For example, if I create a new record and select "Project A" from my dropdown list and no other records exist for this project, ReportID will automatically be set to "1" and ReportNumber will be the unique value "Project A - 1".  At a later time, if I create a new record and select "Project A" again, the ReportID will be set to "2" an ReportNumber "Project A - 2".  In the second scenario, I will also need those fields to be copied from the most recent/last report to the new report, but in different fields.  I.E., if 2 additional fields exist on and on "Project A - 1" the field "PreviousAmount" is "0.00" (which will also need to be automatically entered or left blank if no previous reports exist), and "CurrentAmount" when I create the record with the ReportNumber "Project A - 2", I need the "PreviousAmount" field to automatically be set to the "CurrentAmount"  from the most recent report/greated ReportID for that specific project.

       

      I'm having a hard time trying to figure out how to get the calculated fields to do calculations based ONLY on the most recent/highest ReportID record that is for the same project.

       

      I run into issues because the ReportID and Project will both occur multiple times, and only the ReportNumber will be a unique value.

       

      This was really hard to explain but hopefully it makes sense!  I think they should be similar issues to solve.  I am SO stuck.  ANY guidance is GREATLY appreciated.

       

      Thank you!!

        • 1. Re: Calculated field to equal specific field from specific record?
          philmodjunk

          Having such a serial number that starts over with each new set of records (a project in your case) is a process that can be quite tricky to set up so that if functions reliably--especially if you are sharing your database over a network and might have more than one user creating such records at the same time. The best solution is to not identify your records this way. A simple serial number field that increments with each new record--regardless of what project it is--is much simpler to implement and you should have such a field and use it to link any related tables/records even if you use the calculated report ID that you describe.

           

          What tables have you defined for your database? How are they related?

           

          It looks like you should have at least two tables here. One table should have one record for each project and the other would be your "Project details" records that hold the data you describe in your post.

           

          I suspect that some simple running total summary fields can provide the results you want for the second part of your question as long as you use finds and sorts to properly group your records--but I need to know more about your database to be sure.

          • 2. Re: Calculated field to equal specific field from specific record?
            jomonster8u

            Hey Phil,

             

            Thank you so much for the reply..

             

            I don't have any other tables in my database except for one that is a portal line-item type style (think the Purchase Order sample file that comes with filemaker).  The fields I have in this table are:

             

            "AlottedAmount" - this is the same for every report, but different for each project.  I think I need to include this information in my "Projects" file as it is job specific.

             

            "PreviousAmount" - the total for "CurrentAmount" of every report for that particular project, this could be a summary field if I knew how to make it project specific

             

            "PercentageComplete" - a user entered field

             

            "CurrentAmount" - the new amount for the report, calculation field, (TotalAmount - PreviousAmount)

             

            "TotalAmount" - the total amount of current and previous amount, calculated field (PercentageComplete*AlottedAmount)

             

            The only other additional info I need for these reports is project specific, and that is stored in another (huge) database that has tons of VERY specific project information.  This database, though it only has one table, I thought would be way too specific to bother including in the other database, as all I really need from it other than the dropdown menu with the project numbers is maybe 3 related fields (which come from the same table in that file, but are linked to several other tables).

             

            I couldn't think of a better way to break this up into multiple tables.  I see what you're saying about identifying the records on their own, and yes, it will be used on a network, most likely only by one person at a time, but i do think its best to prepare that this may not always be the case.  To solve this, I can, like you mention, create an ID field with an automatically generated serial number.  This will make it easy to find the report, too, if i ever need to look it up later.

             

            If it were just for me to look at, I have no problem peeking at a previous record to get what I need to do the report, but the problem is that I am creating this database to help me fill out a form that already exists, and it's not one we can change (or I would a long time ago!!).  I cannot change this form, and it must include the above fields for each item

             

            Its genius to use summary fields, but how do I make the summary fields project specific?  In other words, how would I tell a summary field to only add up fields that are "Project A"?  Variables maybe...?

             

            Again, thank you so much for the help - I have no one else who understands nearly half as much as I do about this (and that's really not much, lol)

             

            Thanks!!

            • 3. Re: Calculated field to equal specific field from specific record?
              philmodjunk

              I'm not entirely clear about how you plan to use this database. It seems like you have some data that is common to all records of a given project and others that are not.

               

              "AlottedAmount" - this is the same for every report, but different for each project.  I think I need to include this information in my "Projects" file as it is job specific.

              This sounds like a field that should not be in this table. It should be in a projects table where you have just one record for every project. You can use a relationship to link a record in this table to the table we are discussing in this thread.

               

              "PreviousAmount" - the total for "CurrentAmount" of every report for that particular project, this could be a summary field if I knew how to make it project specific

               Do you need this field on your report or do you just need it for your calculations?

               

              "PercentageComplete" - a user entered field

               

              "CurrentAmount" - the new amount for the report, calculation field, (TotalAmount - PreviousAmount)

               

              "TotalAmount" - the total amount of current and previous amount, calculated field (PercentageComplete*AlottedAmount)

               

              I'm reading this as a kind of "progress" or "task" ledger where each new record documents new work completed for an ongoing project. SInce you haven't identified which version of filemaker you are using, I'm answering in terms that fit for Filemaker 9 - 11. The method works for older versions if they support relationships with inequalities, but some details in how you set this up may differ.

               

              I suggest two tables: Projects, ProjectDetails.

              Fields in Projects:

              AllottedAmount,

              ProjectID (Auto-entered Serial Number)

              ProjectName

               

              Fields in ProjectDetails:

              ProjectID (number field)

              PercentageComplete (Number)

              TotalAmount ( Calculation PercentageComplete * Projects::AllottedAmount )

              DateLogged (Date)

              CurrentAmount (Calculation, TotalAmount - PrevProjectDetails::TotalAmount) //Define relationships below, then come back and create this calculation

               

              Relationships

              Projects:: ProjectID = ProjectDetails:: ProjectID

              ProjectDetails:: ProjectID = PrevProjectDetails:: ProjectID AND

              ProjectDetails:: DateLogged > PrevProjectDetails:: DateLogged   (Sort relationship by DateLogged, Descending)

               

              PrevProjectDetails is a 2nd Table Occurrence of Project Details. If you haven't done this before, you go to Manage | Database | Relationships and click the box for ProjectDetails to select it, then click the button with two plus signs to make a copy of it. Rename it PrevProjectDetails and link it as described above.