Jump to content

Complex Calculation

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

Recommended Posts

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 :


Link to comment
Share on other sites

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:

Link to comment
Share on other sites

: 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. :confused:

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

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

This topic is 6673 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.