Understanding calculations and related records

[Alex Quinlan]

Hi,

 

I tried to make a simple calculation to total up outstanding bills or return some text if all paid.

 

Invoice dates are only created on receiving payment.

If ( IsEmpty ( Finance::date invoice ) ;

 Sum ( Finance::calc_paymentdue ) ; 

"All Bills Paid" )

 

 

It comes back with all bills paid even though there are a number of records with empty Date Invoice fields.

 

Does this look at all related records and if it finds any Date Invoice field with data it returns the text? 

 

Any help appreciated.

Thanks

[comment]

Filemaker has no SUMIF or COUNTIF functions. Your calculation looks at the first related record: if it has a date, then it sums ALL related records; otherwise it does nothing.

 

One way to solve this problem is to define a calculation field in the Finance table along the lines of =

Case ( not date invoice ; calc_paymentdue )

and sum this field.

 

 

If you only want to "return some text if all paid", then count the date invoice field, and compare the count to count of related records.

[Fitch]

Your IsEmpty( ) is only looking at the first related record. Try something like this:

IsEmpty( List( Finance::date invoice ) )

[Alex Quinlan]

Thanks guys for helping me understand this.

 

I ended up creating the calculation field in the finance table. 

Case ( IsEmpty ( date invoice ) ; Sum ( calc_paymentdue );
not IsEmpty ( date invoice ) ; " "  )

[comment]

 

I ended up creating the calculation field in the finance table. 

Case ( IsEmpty ( date invoice ) ; Sum ( calc_paymentdue );
not IsEmpty ( date invoice ) ; " "  )

 

 

 

That's a pretty convoluted way to write;

Case ( not date invoice ; calc_paymentdue )

Note:  the Sum() function in your formula is "summing" a single local value, therefore it's redundant. So is the second test in the Case() function: if the first test (IsEmpty) is not true, then of course the second test (not IsEmpty) must be true. Why you need a space as a default result is not clear to me.

[Alex Quinlan]

Ok that makes sense. I thought I would need to give the calculation what to do if the result was true. Thanks for clarifying!

[Alex Quinlan]

As this is about the Case function I'll ask here rather than starting a new thread.

 

I have made this calculation:

Case ( 
Stage of Development="Enquiry" ; "1" ; 
Stage of Development="Appointment" ; "1" ; 
Stage of Development="Outline Proposals" ; "1" ; 
Stage of Development="Pre-App" ; "2" ; 
Stage of Development="Planning Application" ; "2" ; 
Stage of Development="Final Proposals" ; "2" ; 
Stage of Development="Working Drawings" ; "3" ; 
Stage of Development="Tender Period" ; "3" ; 
Stage of Development="On Site" ; "4" ; 
Stage of Development="Defects Period" ; "5" ; 
Stage of Development="Completion" ; "5" ; 
"BROKEN"
)

Unfortunately it always come back with BROKEN. I don't understand why it isn't working. 

 

[Lee Smith]

You don’t need the quotes on the numbers

[doughemi]

What kind of field is Stage of Development?  Checkbox?

[comment]

What happened to my answer? Which said:
 

 

There is nothing wrong with your calculation per se, so check your data.

 

[Lee Smith]

See your private messages. 

[Alex Quinlan]

Thanks for the responses.

 

Stage of development is a text field with a value list.

 

I removed the quotations and I still have the same problem.

 

 

The value lists matches the results in the Case calculation to the letter and capitalisation - not sure if that matters.

Perhaps I'm not understanding how the Case function works?

 

 

I am trying to give a numerical value to the stage of development to be used in conjunction with a progress bar.

 

Any help appreciated.

[bruceR]

Do you still have not described how the field is displayed. Check box? Radio buttons? Dropdown list?

Try placing a copy of the field on the layout and set it to be just a standard text box.

Does it contain returns? Does it contain other values?

[Alex Quinlan]

Sorry I missed that - It is a pop down menu. 

 

I have set both the calculation field and the stage of development field to a standard edit field and it still returns BROKEN.

 

 

There are no returns in the value list and they match exactly the results in the Case calculation. Inputting into the field Stage of Development manually rather than a pop down list also returns BROKEN.

[bruceR]

As Comment says; there is nothing wrong with the calc.

 

Sounds like it is time to post your file.

[Lee Smith]

In the interest of time, see the attached.

Alex Quinlan.fmp12.zip

[Alex Quinlan]

Thanks for taking the time Lee.

 

I understand what BruceR was getting at with his questions. In my value list I had managed to include a space at the end of each value.

So not exactly the same!

 

I appreciate all the help. Hopefully I wont make this kind of mistake again. Wishful thinking perhaps.

[Lee Smith]

Spaces in Fields, Lists, Relations, etc. names,  will mess you up. Welcome to the world of developing. Don’t worry about making this kind of mistake again, YOU WILL, it is part of the fun.

 

Lee