Total By Poject at a specific date

[gaby78]

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

[mr_vodka]

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, 2007 by Guest

[gaby78]

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 ?

[mr_vodka]

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.

[riseagainstme]

isn't this what you are trying to do?

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

Edited March 12, 2007 by Guest

[mr_vodka]

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.

[gaby78]

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:

[riseagainstme]

gotcha,

sorry missed that part

Edited March 13, 2007 by Guest