3 Replies Latest reply on Dec 14, 2012 3:14 PM by philmodjunk

    Set/Update a Field

    C.AllenCrawford

      Title

      Set/Update a Field

      Post

           Got some help here to get the Case statement below. Just learning the software please bear with me...

           The Case statement posts a field with an amount.... In another table I have a field which I want to keep a cumulative amount to date after each posting. The format in the FM Help showed me the second statement which I filled in but doesn't work.

           I have a cumulative amount field in table 2 and a weekly posting in table 1. When an amount meets the date test in table 1, I want to update the table 2 field to keep a cumulative annual amount.

            

           Case(
           Date> Date ( 12 ; 31 ; 2011) and Date < Date ( 1 ; 1 ; 2013 ) and Type="Pledge" ; Amount
           )
           Set Field [Table 2::${2012CumPledge}; Table 2::${2012CumPledge}+${12PledgeWK}]

        • 1. Re: Set/Update a Field
          philmodjunk

               Your case function could be simplified to:

               Case ( Year ( Date ) = 2012 and Type = "Pledge" ; amount )

               Set Field, BTW, is a script step so you would not enter that in a "Specify Calculation" dialog box.

               But note how you 'hardwire" your field to a specific year. Do you plan to add a new field with each new year? That can get really complicated and there are better ways to get a yearly total.

               Here's a solution that keeps it all in one table--yes there are reasons for using a related table, but let's try the simplest approach first:

               Define a summary field in your table to compute the total of amount.

               Define a calcualtion field, cYear as: Year ( Date )

               Create a layout and give it a new layout part called a sub summary layout part. Select cYear as the "when sorted by" field.

               Delete the Body layout part from this layout.

               Put cYear and the new summary field inside the sub summary layout part.

               Perform a find for the records that you want. Include the criteria that type must be "Pledge". You can also specify a date, range of dates, year or range of years as part of this find. If you perform such a find manually, you may need to do so on a different layout based on the same table before cahnging to this layout. Such a find can also be scripted to use data specified by the user in global fields.

               Sort the resulting found set by cYear. and you get a a report where each year's total pledges (for the dates specified) are listed with one row for each year.

               PS. if you must pull these totals into a different table, you can use a relationship that matches to records of a specified Type and Year to get the same yearly totals. In FileMaker 12, this can also be done with ExecuteSQL.

          • 2. Re: Set/Update a Field
            C.AllenCrawford

                 Yes, I was planning to add a new field each year and there are 2 tables. I was given this software with this application when I took over as Treasurer for our church. This is the way it went...here is the software, here are 2 screens and here is how to input the data and run a couple of reports. I am trying to figure out how this software works and maybe will try to rebuild the thing some time. For now, it is nearly year end and I am just trying to find a way to get cumulative totals for 2012 so I can give donation letters out for IRS purposes. All I am trying to do is update a cumulative field with each weekly donation. I learned UNIFY DBM on a UNIX operating system MANY years ago but things have changed. I'm just trying to figure out the basics for now. Do appreciate your help!!

                  

            • 3. Re: Set/Update a Field
              philmodjunk

                   Adding a new field each year is not a good idea.

                   What I am describing is the simplest way to do what you describe. And the find you perform can either specify a specific donor or produce a report where each donor's donations for the year are listed under a heading with their name and other info as a header--with each header starting a new page so you can print it all out as a batch print job and then send each donor their year end giving summary.

                   In cases where you want to see the individual donations listed with a total, keep the body layout part instead of deleting it like I suggested in my last post.