How to use External Data in a calculated field

Help!  We have a customer requirement to use data from an External system and Sage CRM to calculate the available credit of a customer for order processing.

The Sage CRM orders are converted to invoices and imported into the external system.  Therefore, the calculation includes quotes and orders that HAVE NOT yet been converted and imported. 

We added a panel to the company summary screen to display this information. We have also added buttons to the summary screen to allow users to create a quote or order from the company summary screen.  This is what the panel looks like.

Sorry that is not a very good image.  This is what is represented there

Credit Limit  -   Outstanding Amount  -   Quotes  -   Orders  -   Invoices = Available Credit

I have added the calculation for Available Credit to a view which is used for the panel.  Therefore, this data is pulled fresh each time the company summary screen appears.

I am having two issues with the calculation and I am hoping that someone can push me in the right direction to resolve these as this solution is an import and time saving piece of their order process.

My first issue is with the amounts from the external database.  They are Credit Limit and Outstanding Amount.  If I include these fields in my calculation, my available credit comes back as 0 when there should be a value.  The fields are numeric fields.  However, I have CAST them as Money in my view.  However, I still get a zero.  What can I do to get the amounts properly included in my calculation.

My second issue is with the amounts from CRM.  If one of the amounts for Quotes, Orders or Invoices is 0, my Credit Limit is returning zero when there should be a value.

If you can see the screen shot, you will see that the following data i populated there..

Credit Limit  -   Outstanding Amount  -   Quotes  -   Orders  -   Invoices = Available Credit

100000.00  -         0                           -    560.49   -  25,932.79  -   0        =  -26,493.28

This is what I have in my view currently in order to get the -26,493.28 to appear.  This is missing the fields that cause a 0 to be returned.

(-t3.quot_nettamt - t4.orde_nettamt) as comp_availablecredit,

Has anyone else attempted to combine fields from an external database with CRM fields in a calculation and came up with the correct values?

If so, can you let me know what I might need to do to get this to work.

Any assistance is greatly appreciated.  Thanks!

Parents Reply Children
No Data