Complex Calculation

[Mushi]

I'm not sure whether i should put this in the Portal forum or not : , but i was wondering how to do a complex calculation of fields in a portal.

I have a record for each part, and the portal shows various transactions that have occured, such as recieving and dusbursing and the quantity involved in each transaction. I want to create a 'balance' field that shows how many parts we have. I want it to add up the quantity of parts we received and subtract the quantity of parts we disbursed.

I tried the Sum function and put an If statement inside it, but it just seems to add up all the quantitys (and not subtracting). I just don't know enough about the different calculation functions.

Thanks for your help :

Mushi

[LaRetta]

I assume received is a positive number and disbursed is a negative number in two different fields?

I think the simplest would be to create Summary fields in your portal table because you can then use these summaries elsewhere ... and total counts of your 'lineitems' will be something you'll frequently want anyway.

sTotalReceived = Summary = Total of Received

sTotalDisbursed = Summary = Total of Disbursed

Then in your main file, create a calculation called cBalance (number) with:

sTotalReceived + sTotalDisbursed

I made the assumption that you don't use both Received and Disbursed in the same quantity field; otherwise you could have just used Sum() in your main file. If your Disbursed is a PLUS, use a minus in the above calculation instead of a plus. But you didn't give us much to go on. If you instead have a 'type' field indicating whether each line is Received or Disbursed, let us know and we can change our suggestion for you. :wink2:

[Mushi]

: no, they both a positive numbers.

The records in the portal have a quantity field and a quantity type field, where there is a radio button set that you can choose receiving or disbursing. it seems that if i create a summary field in my portal table i can only sum up the quantity and not take into account whether i have received or disbursed.

maybe i should use a script to go through my database and set the disbursing quantitys to negative numbers?

[LaRetta]

I believe that, with both numbers positive, you'll run into this issue time and again. I would change your Disbursed to negative. Then you'll only need one Summary field which can be placed directly in your Main file, eliminating the calc in Main entirely. If Disbursed is properly a negative number, reports etc. will be a piece of cake, adding and subtracting the quantity appropriately.

Otherwise, there are a few ways to address it, each adding to the complexity of it:

1) Use an additional new number field (Auto-Enter Replace) in your child table which converts when appropriate, ie, Disbursed automatically changes to minus but Received leaves it positive. Then base your Summary (total of 'this new field') in Main.

2) Use table occurrences of each to main to split them, ie, Main would require two new fields: "Received" and "Disbursed". Then join Main::Received to Child::Type and call the TO 'Received". Repeat with Disbursed.

... and there are probably other ways. But if you minus your Disbursed, that same quantity field will always produce your balance for you with no additional work. It depends entirely upon your business needs, entire solution structure (whether you have Inventory modules, etc.) none of which you've mentioned. :wink2:

[Mushi]

:thanks:

Thank you for all of the great help!