Skip to Content
author's profile photo Former Member
Former Member

Wrong results from Query !!

Hi All

I have problem and I can't seem to get it sorted , I want a quesry that will get values from six different UDF's from BP Masterdata hearder level.

All fields are set to numeric as value entered are numerics. Please see the field names below..

U_BP_OWNERSHIP

U_BP_EMPLYEQUITY

U_BP_MANAGEMENT

U_BP_SKILLSDEVELOP

U_BP_ENTERPRISEDEVEL

U_BP_PREFERPROCUREM

value on all the above field's must be added and total be displayed in the below field...

U_BP_BBBEEETOTAL

I posted a thread before and so far I never manage to get it right please find the query I used for this ..

SELECT $[OCRD.U_BP_OWNERSHIP] + $[OCRD.U_BP_EMPLYEQUITY] + $[OCRD.U_BP_MANAGEMENT]+ $[OCRD.U_BP_SKILLSDEVELOP] + $[OCRD.U_BP_ENTERPRISEDEVEL] + $[OCRD.U_BP_PREFERPROCUREM]

When I use the above query linked to the U_BP_BBBEEETOTAL field i get a concatenated figure instead of a totaled figure.

e.g.

U_BP_OWNERSHIP = 12

U_BP_EMPLYEQUITY = 23

U_BP_MANAGEMENT = 23

U_BP_SKILLSDEVELOP =34

U_BP_ENTERPRISEDEVEL =34

U_BP_PREFERPROCUREM =10

U-BP_BBBEEETOTAL = 122323343410

Where did I got wrong with the query.. please assist.

Thanks

Bongani Dlamini

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Nov 10, 2008 at 04:04 PM

    Try this query:

    SELECT $[OCRD.U_BP_OWNERSHIP.Number] + $[OCRD.U_BP_EMPLYEQUITY.Number]
     + $[OCRD.U_BP_MANAGEMENT.Number]+ $[OCRD.U_BP_SKILLSDEVELOP.Number]
     + $[OCRD.U_BP_ENTERPRISEDEVEL.Number] + $[OCRD.U_BP_PREFERPROCUREM.Number]

    Add a comment
    10|10000 characters needed characters exceeded

    • I'm not sure I understand your requirement clearly.

      If you want to set the total field to the sum of these fields, you should connect the query as a formatted search to the total field. When you enter Shift+F2 on this field (or double click in version 2007A), the field gets the total value.

      The problem is that you can not make automatic this action, because you can not set that the FS fire automatically on changing any of the fields. You should instruct the user to use Shift+F2 or define a sequence entering the field values and connect the FS to the last one.

      The steps to define the FS:

      1. Save the query

      2. On the total field use the AltShiftF2 key combination

      3. Set on the user defined values setup window choose:

      a. Search in Existing User-Defined Values according to Saved Query

      b. select the saved query

      c. if you choose the second method, set Auto Refresh and select the last modified field

      d. set Display Saved User-Defined Values and

      e. Update

  • Posted on Nov 11, 2008 at 05:05 AM

    hi,

    Number 
    
    Use Number if the required field includes a number + a currency symbol (item
    price, document total), to retrieve the number (without the currency symbol or the
    unit of measurement). This separation is essential when the user wishes to use
    the price for arithmetic expressions.

    Jeyakanthan

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 11, 2008 at 09:10 AM

    Sorry for the long struggle but no result. I have found out that only this kind of query can work:

    SELECT T0.[U_BP_OWNERSHIP] + T0.[U_BP_EMPLYEQUITY]
     + T0.[U_BP_MANAGEMENT]+ T0.[U_BP_SKILLSDEVELOP]
     + T0.[U_BP_ENTERPRISEDEVEL] + T0.[U_BP_PREFERPROCUREM] 
    FROM OCRD T0 WHERE T0.CardCode = $[$5.0.0]
    

    The $ sign may convert the UDF to alphanumerical by default. There is no cure yet.

    Thanks for your patient.

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 11, 2008 at 02:57 PM

    Hi Istvan

    There is one small problem now ... I want to link the total to the marketing docs and when I run this query

    Select $http://OCRD.U_BP_BBBEEETOTAL

    I get this error

    11/11/2008 16:53:00: Internal error (-1003) occurred Message 131-183

    Thanks

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.