1 Reply Latest reply on Aug 24, 2010 9:24 PM by MikhailEdoshin

    using a GetSummary field in a script

    vmtc

      Title

      using a GetSummary field in a script

      Post

      Hi, 

      Is it possible to include a Get Summary field in a script? 

      I am trying to work out payments to authors based on their titles. I want to find records which are due for payment and mark them as payable.

      A  payment is based on a title which has its own statement. However, a recipient may have more than one title. Payments are only paid if the total owing is more than $100. Otherwise they are carried forward to the next period. 

      It is possible to have a negative result on a statement but this is not subtracted from the total to be paid.

      A GetSummary field, sorted by Address ID  finds when the total payment is above $100.

      My thinking was that I should write a script with the following steps:

      1. Find the records where the amount due was >0 (eliminating the negatives which are not included in the calculation)

      2. Sort found set by the Address field. This would populate the Get Summary field (ie  summary of amount due when sorted by address field).

      3. Constrain the found set  for values in GetSummary field > 100.

      4. Set variable "Yes" in a 'To Pay' field.

      However when I tried doing the find, sort and constrain manually, the constrain said no records matched (there are ones that do). I'm sure this is to do with the fact the field needs to be sorted before calculating a value, but don't know how to get around it.

      Can anyone help?

      I'm using FM 10 Adv on Mac OS but it is used on Window XP, Vista and 7.

        • 1. Re: using a GetSummary field in a script
          MikhailEdoshin

          It's possible to use GetSummary in a script, but it's not possible to use it to find anything. Summary calculations are defined against the current found set; in Find mode there's no found set yet, so they don't evaluate to anything interesting.

          I see two variants: a) establish a sorted found set and then loop through it, read subsummary for each group, and omit groups that are below the threshold, or b) do the math from the authors table. The latter looks somewhat simpler to me. I don't quite get the schema, but I suppose it's like that:

          Author -< Title -< Payment

          I.e. one author has many titles, and each title has many payments. We only need to consider outstanding payments, i.e. those that don't have “Yes” in their ‘To Pay’ field. Let's make a calculated field Outstanding Amount:

          Case( To Pay ≠ "Yes", Amount )

          Now we can summarize these fields from the Author table::

          Sum( Payment::Outstanding Amount )

          This calculated field is searchable. The idea of the script is to find all authors whose outstanding amount is greater than 100, Go to Related Records in the Payment table (matching all authors in the found set) and here are all the payments you're looking for.