Jump to content

Conditional count function ?


This topic is 6535 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello,

Is there a way to make a conditional count using a single script ?

For example:

Count ((Invoices::Balance) > 0 )

The above line is not accepted. The intension is to count all records whose balance is non zero.

Of course this could be done by setting a seperate calculation field as a flag and then counting the flags .... but can it be done more elegantly by script without introdusing new fields?

Link to comment
Share on other sites

I'm slightly puzzled here, the payment is done in the table for invoices? Are you accepting partial payments for one invoice?

Well

Show All Records 

Go to Record/Request/Page [ Last ] 

Loop 

     If [ invoices::Total > invoices::Payment ] 

           Omit Record 

    Else 

           Go to Record/Request/Page [ Previous; Exit after last ] 

    End If 

End Loop 

Show Omitted Only 

Show Custom Dialog [ Title: "Invoices whose balance is non zero."; Message: Get ( FoundCount ); Buttons: “OK” ] 

Show All Records 

--sd

Link to comment
Share on other sites

SD,

Thank you for this hint. I will give it a try using your script suggestion.

About the invoices (sorry to confuse you) it was chosen by way of example. In reality it is the "projects" table and tracks all info about a project including total value and payments received to date. So we need to know (just as a statistic) how many projects still have outstanding balances.

Again, many thanks for your help :B

Best Regards,

Thomas

Link to comment
Share on other sites

For a "live", self-updating calculation, you need to add that extra field.

If you prefer a static snapshot, you can simply do a find for >0 in the Balance field.

Thank You.

Yes, "live self-updating" calculations are always a high priority. This particular count will be a numeral (live statistic) on top of a portal.

Regards,

Thomas

Link to comment
Share on other sites

This:

For a "live", self-updating calculation, you need to add that extra field.

...kind of defeats the object with this:

but can it be done more elegantly by script without introdusing new fields?

...but whether it's more or less elegant, won't I be the judge!

--sd

Link to comment
Share on other sites

...Alternatively, you could add an extra field in your related table: Calc field, with result:

If(Balance = 0 ; "" ; "A")

And then count this from your other side.

The reason this will work is because count only counts records where that field is not empty.

~Genx

Link to comment
Share on other sites

This topic is 6535 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.