Hello everyone:

I am having trouble getting a total by project at a specific date.

In nutshell what I need is an output (onscreen or columnar, no preference) that shows the Total amount for each project at a specic date (June 30 2006 or Oct 31 2006) omitting projects with zero balance. See attached file.

Thanks for any feedback and help.

AmtByProj.zip

Ed,

Why dont you enter the second criteria into your find request? IOW,

YourDate < 6/30/2006

ProjBal > 0

Then you can run your summary report.

http://www.filemaker.com/help/03-Finding%20sorting10.html

Edited March 12, 200718 yr by Guest

Thanks for your feedback Mr. Vodka.

Using the ProjBal field would also eliminate projects that had a balance at the cutoff date and were paid off after the cutoff date. In other words a zero balance in that column does not necessarily mean that the balance was zero at the cutoff date (ie June 30). When I filter by cutoff date the pink field (which is a cal field with GetSummary) gives the right amounts (not the Proj Bal field). My aim is to eliminate any project that shows a zero in the pink field. I tried to use it but it just would not filter. It seems that a Getsummary field can’t be used in find request ?

Ok I gotcha now. I guess you could write a loop script to omit the records with zero summary totals for the found set, after the sort step.

Sort Record [Restore; No dialog]

Go to Record/Request/Page [First]

Loop

  Set Variable [ $ProjNum = Invoices::ProjNbr ]

  If [ Invoices::ProjNbr = 0 ]

    Loop

      Exit Loop If [$ProjNum  ≠  Invoices::ProjNbr]

      Omit Record

    End Loop

  Else

    Go to Record/Request/Page [Next; Exit After Last]

  End If

End Loop

I havent really thought the whole thing through so there may be a more efficient method.

isn't this what you are trying to do?

Constrain Found Set [action: Omit Records, ProjNbr=0]

Edited March 12, 200718 yr by Guest

isn't this what you are trying to do?

Constrain Found Set [action: Omit Records, ProjNbr=0]

This won't work because you can not find on a summary field. Constraining a found set is basically entering in another find criteria.

I am using FM7 (can’t use variables in scripts)… I modified your script and it works great. I never thought of using a script to omit the zeros. Thanks Mr. Vodka. :thumbup:

gotcha,

sorry missed that part

Edited March 13, 200718 yr by Guest