Case function, unexpected result

[Oyseka]

Hi, I have a small Bar & Restaurant billing and stock system designed in FMP 5.5. The item sub total on the Invoice is in a portal from the line items file and should be modified by the account type determined on the invoice.

i.e. If the account type is cash or account then the item sub total is just the item price * the quantity, if the account type is spillage or social then the item sub total should be zero(0). I have defined the calculation for sub total in line items as:

Case(Acct Type="Cash";Qty*Price; Acct Type="Account";Qty*Price;0)

The Account Type field, which is a value list, in line items is from the invoice given by the relationship Invoice number. I am however getting zero in some instances where there should be a value.

Can anyone help please

Chris

[Søren Dyhr]

It seems like you have a migrated solution where portals freshing is moved somewhere else ...by and large will portals have freshing issues under all versions due to the data being a relation way.

Explain in which of tables does each of the fields belong to???

If it's an individual file as it used to be, put the table into same file, by recreating it inside, and some of the freshing issues will resolve.

--sd

[IdealData]

The logical execution of the Case function has changed in FMP7.

Before it would evaluate ALL references, regardless of need and was inefficient (albeit reliable). The CASE function now stop executing at the first "correct" result. This also applies to several other sequential logic functions (eg If) and has been done to improve calculation speeds.

As the CASE function ceases executing before reading all conditions then it will not re-evaluate existing data if they did not meet their case.

[LaRetta]

Before it would evaluate ALL references, regardless of need and was inefficient (albeit reliable).

7/8 provides MORE reliability. Because Case(), If(), AND, OR stop evaluating when they hit the first true (short circuits), it provides the added benefit of allowing a natural filter which, in the past, required much more complex and/or/not constructs. Records are eliminated after first true so they don't need to be included further down in the formulae (in combination with other test criteria) to eliminate them again.

Because of short circuiting in 7/8, branch prediction (eliminating the greatest number of records in the first test) becomes a powerful (filtering) tool. Reliability is dictated by logic. And the new behavior is much more logical and blazingly fast in comparison to prior versions.

LaRetta

[Oyseka]

This particular system is still running under FMP 5.5 as the user is still on Windows 98 (neolithic I know but this is Africa !) so there are no migration issues

[Oyseka]

It seems like you have a migrated solution where portals freshing is moved somewhere else ...by and large will portals have freshing issues under all versions due to the data being a relation way.

Explain in which of tables does each of the fields belong to???

If it's an individual file as it used to be, put the table into same file, by recreating it inside, and some of the freshing issues will resolve.

--sd

This solution is still running under FMP5.5 due to the user still being on Windows 98 and consists of seperate tables (files), i.e. Invoice, Items, Line Items. The Account Type field is in the invoice file, the quantity and price fields are in the line items file

[Søren Dyhr]

Alright see how Don Wieland does it:

http://www.dwdataconcepts.com/dl/tw/UPDATE.ZIP

--sd