Calculations of calculations

[Gabriela]

Hi there,

I have a number of related files creating a stock control system: stock goes in, stock goes out and what's left in the warehouse. There are two warehouses, stock is counted in cases, but also in units as a total of both warehouses.

My problem is that some of the calculations that use results from other calculations don't show up properly. They show minus figures, when I refresh the screen the figures are correct, but they revert back to the minus figures when printed or viewed in preview mode.

These are the calculation fields that show up correctly:

SumCasesRecPhoe Unstored Calculation =Sum(CasesOut::CasesReceivedPhoe)

SumCasesShipPhoe Unstored Calculation =Sum(CasesOut::CasesShippedPhoe)

CasesInStockPhoe Unstored Calculation =SumCasesRecPhoe-SumCasesShipPhoe

SumCasesRecAber Unstored Calculation =Sum(CasesOut::CasesReceivedAber)

SumCasesShipAber Unstored Calculation =Sum(CasesOut::CasesShippedAber)

CasesInStockAber Unstored Calculation =SumCasesRecAber-SumCasesShipAber

Based on those calculations above I'm running another calculation that gives me the total units in stock, and this does not show up properly in preview mode and in browse only when screen is refreshed:

UnitsInStock Unstored calcuation =UnitsInCase * (CasesInStockPhoe+CasesInStockAber) by the way, UnitsInCase is just a normal number field

any help would be very much appreciated.

:-)

[Paolo]

Every record is an item?

UnitsInCase have correct values?

Was the file always closed properly in the past. Or did sometimes consistency check/recovery took place ? (In other words, may the db be corrupt?)

What do you exactly mean by "does not show up properly in preview mode and in browse only when screen is refreshed"

[Gabriela]

Hi Paolo

Every record is an item?

yes, every record represents a product.

UnitsInCase have correct values?

yes

Was the file always closed properly in the past. Or did sometimes consistency check/recovery took place ? (In other words, may the db be corrupt?)

Yes, it could be corrupted, I'll look into going back to an older backup version, although i think the problem was there before.

What do you exactly mean by "does not show up properly in preview mode and in browse only when screen is refreshed"

Well, for example: if CasesInStockPhoe is 58 and CasesInStockAber is 358 and the UnitsInCase is 4, the result should be 4*(58+358)=1664. when I open the file first TotalUnitsInStock shows (minus) -2876 (I haven't got a clue where that figure comes from. However, when I click somewhere on the window. The figures 'refresh' and the correct figure of 1664 shows. However, when I now try to print this it print -2876.

Thanks for your help, If you can shed any more light on this that would be great. In the meantime I'll try to figure our if this could be a corruption issue.

Gabriela

[Anatoli]

Try that calculations with expected number of records. If you hit higher numbers, you will find that it will be slower and slower.

In short -- do NOT calculate across number of records.

Store static values in fields and in any number of records it will be blazingly fast. With 500k records you'll be waiting minutes when doing search.

Search in static system 2 million records will be instant.

[Paolo]

Could it be a simple layout problem? Maybe you've placed two fields on the same place...

[Gabriela]

Hello Paolo,

You were right. the file was corrupted. I recreated a simply file to simulate the same calculations and it works perfectly all right.

Unfortunately, whenever I receive the files from my client and open them, it tells me they weren't shut down properly. I'll have to investigate what he's doing that causes this, to avoid the problem in the future.

thanks for your help and sorry I haven't been in touch earlier, I've been in client meetings all of the second half last week.

Anatoli, I sort of understand what you're saying and I will pay some attention to the way calculations and summaries are created, as speed is sometimes a problem.

thanks to all of you.

Gabriela