How to use External Data in a calculated field

SOLVED

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
  • 0

    Update to this.  It appears that the calculation is correct if the value in the field is not 0.00.

    I updated my view to the following and got 73,506.72 which would be correct.

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

    Therefore, I only have one issue and it is the zero balances.  If I update my view to the following...

    (CCRDLMT - CTARTOT-t3.quot_nettamt - t4.orde_nettamt) as comp_availablecredit, the available credit returned is zero.

    The value for CTARTOT is actually NULL rather than zero as displayed on the screen.  I am guessing that is my issue.

    Therefore, I need to figure out how to replace NULL with zero in my view. 

    If anyone knows how to do this, I would greatly appreciate assistance. 

    Thank you for looking! :-)

  • 0 in reply to Michele Gaw

    Michele

    You can use 'isnull' within the view to treat any null value as a 'zero'.  e.g.

    select Oppo_Description, isnull(Oppo_Total,0) from Opportunity

  • 0 in reply to Sage CRM

    Thanks Flat Stanley!

    I did try isnull and although it does return a zero for the field value rather than a NULL , the over all calculation still returns NULL.  Below is how it looks in Management Studio.  The calculation only works if I take the fields with a zero value out of the formula. 

    If it was only happening with the field from the external database, I would think it had something to do with the data type.  However, I only have a problem with the fields that have a zero in them.

    Any idea how to fix this?  I understand why the calculation would not work with NULL but I am currently at a loss as to why it does not like the zeros.

    Any assistance is greatly appreciated.  Thank you!

  • 0 in reply to Michele Gaw

    Could you try using numeric as the datatype rather than float?  Numeric is the datatype used within Sage CRM.

  • 0 in reply to Sage CRM
    SUGGESTED

    You cannot use "as abc" and reuse abc to do calculation. See this below getting error. Yours was not getting error because you are using the same name. See the second example is similar as yours. Your line 0, 1 and 2 the CCRDLMT and CTARTOT are still getting original NULL value.

  • 0 in reply to Sage CRM

    I had tried numeric and CONVERT as Money, the last thing I tried was float.

    The data type in the external system for its fields is numeric.  However, the data type in Sage CRM for its fields is currency.

    It does not seem to matter if I CAST, COALESCE or CONVERT.  The system still does not seem to like the zero values.

    This might make sense if I was dividing..but I am subtracting.  Weird.

    PS.  I hope the Flat Stanley reference made sense.  Your icon looks like Flat Stanley.  Also not very funny if you don't know who Flat Stanley is.  Flat Stanley is a book kids read in elementary school. As a project, they create a Flat Stanley and take him on adventures.  Your icon looks like the one my son created. Stuck out tongue winking eye

  • 0 in reply to Andrew Liu

    Ahhh.  Ok that helps explain it.  I think I can fix that by putting the calculation in a view that uses the view with the "as" reference.  Going to try that now.  Fingers crossed.

Reply Children
  • 0 in reply to Michele Gaw

    Although I got this to work in Management Studio by creating another view and using that view to do the calculation.  However, it does not work when I place those views in Sage CRM.  I don't get any errors in the system.  However, when you pull up the screen that contains the calculated field, the value is zero.

    When I run that same view in SQL, a value is returned.  Does anyone know why that would be?

  • 0 in reply to Michele Gaw
    SUGGESTED

    Looking at your view I would suggest trying the link to the external table as an OUTER APPLY 

    e.g. SELECT company.*, ExtTable.* 

    FROM Company 
    OUTER APPLY (SELECT ISNULL(FieldA,0), ISNULL(FieldB,0)
                              FROM ExternalTable
                              WHERE comp_companyID = MasterKeyInExternalTable) AS ExtTable

  • 0 in reply to Matthew Shaw

    I don't think I have ever used OUTER APPLY.  Does that change the way the CRM interprets the query?

    If I run the query I have in Management Studio, I get a value returned.  However, when the query goes into Sage CRM, the value is displayed as zero.

    In CRM, I think it might be that the field is currency...but I am not sure.

  • +1 in reply to Michele Gaw
    verified answer

    I got this sorted out, my final view looks like this...

    CREATE VIEW vCompanyCreditCalc
    AS
    Select
    ISNULL(CCRDLMT,0)- ISNULL(CTARTOT,0)- ISNULL(quot_netamtnum,0)- ISNULL(orde_netamtnum,0) - ISNULL(invo_netamtnum,0) AS comp_calculatedcredit,
    *
    from vCompanyCredit

    I greatly appreciated the assistance!