4 Replies Latest reply on Jan 15, 2012 5:18 AM by Sorbsbuster

    Adding a field to a report which adds a value to a date

    pauljohn

      Title

      Adding a field to a report which adds a value to a date

      Post

      I'm new to File Maker, and am trying to modify a report someone had previously created.

      In the database the report is running from is an invoice date. I would like to display on the report/invoice a Due By date, which is the Invoice Date + 60 days, but I can't for the life of me figure out how to do this.

      Could anyone provide a simple solution to this please?

      Thanks in advance

      PJandLiz

        • 1. Re: Adding a field to a report which adds a value to a date
          Sorbsbuster

          Create another field, a calculation that returns a date as its answer, with the calculation:

          InvoiceDate + 60

          • 2. Re: Adding a field to a report which adds a value to a date
            pauljohn

            Thanks Sorbsbuster.

            I've tried creating a field on the report to do this, but as I'm a Filemaker gumby, haven't been able to get this to work.

            I've tried copying a field which uses something like <<Guests::Guests Phone1>> to display a phone number, and using the required Invoice Date field name + 60, but all I maange to get is (what looks like) the current time and the text I've typed in. I can't insert a field from the db as this field doesn't exist.

            Any simple pointers on how to actually drop the calculated field on the report then set up the calc pls?

            Thanks for your help and patience Embarassed

            PJandLiz

            • 3. Re: Adding a field to a report which adds a value to a date
              Sorbsbuster

              "I can't insert a field from the db as this field doesn't exist." - yes, you have to create it.  I will assume you are using FM11.

               

              I assume you have a table in the file called 'Invoices' or something similar, holding all the invoice information.
              You can check this by selecting File -> Manage -> Database and clicking the Tables tab at the top left.  You will see a list of all of the tables contained in the file.
              (In most cases we need to know what tables you have, and how they have been linked together, in order to help you)
              Go to the report layout that is showing the invoice information.
              Choose View -> Layout mode, then Layouts -> Layout Setup and check that it names your Invoice Table as the data source in the box 'Show records from'.
              If that is the case you will now be able to go to back to  File -> Manage -> Database and click the Fields tab.  This will let you define, edit, or delete (careful!) fields in the database.  Specifically, fields in the Invoice table.  (You can choose a different table from the drop-down menu near the top left.)
              You should see listed there the field 'InvoiceDate' (or similar)
              Type the name of the new field, InvoiceDueByDate.  From the drop-down over to the right make it a 'Calculation' field, and click 'Create'.
              You will now get a 'Specify calculation' dialogue.  From the list of fields (in that table) in the upper left double-click the Invoice Date field.  It will be added to the calculation.  Then type + 60 after it.
              Click OK, Ok, etc to finish creating that new field, until you are back looking at the original layout (still in layout mode).
              See if the field 'Invoice Date' is on the layout.  If so, duplicate it.  (You can actually use any field, it will just be easier if the one you start with is already formatted as a date, that's all.)
              You will get a pop-up showing the list of fields in that table, with the field you duplicated already highlighted.  Change it to be the newe InvoiceDueByDate you just created.  Move it to wherever you want on the layout.

              • 4. Re: Adding a field to a report which adds a value to a date
                Sorbsbuster

                That was probably a poor explanation of how to add a calculation field.  Look for 'defining calculation fields' in the Help file and it will be the second item on the list.