SQL Condition Only Working On Some Business Partners

[jimbo1234]

In our company, for all Customers (not Vendors) we need to tick one of Properties 1-14 within the Business Partner (BP) to have complete information. We use B1 Usability Package, and have setup the below SQL Condition on the Add/Update button with BeforeAction set to 'true'. Recently for some BP's, you can tick one or even all of the properties and it still comes up the warning that the condition 'Not Filled' uses. This only seems to happen to a few BPs, as the majority successfully warn you if nothing is ticked, and if user goes back to the screen and selects one or more of these properties, they can add/update successfully.

Any ideas as to what could be happening here?

IF($[$40.2.0] = 'C' and $[$136.2.0.1] = 'N' and $[$136.2.0.2] = 'N' and $[$136.2.0.3] = 'N' and $[$136.2.0.4] = 'N' and $[$136.2.0.5] = 'N' and $[$136.2.0.6] = 'N' and $[$136.2.0.7] = 'N' and $[$136.2.0.8] = 'N' and $[$136.2.0.9] = 'N' and $[$136.2.0.10] = 'N' and $[$136.2.0.11] = 'N' and $[$136.2.0.12] = 'N' and $[$136.2.0.13] = 'N' and $[$136.2.0.14] = 'N')
BEGIN
SELECT 'Not Filled' FOR BROWSE
END

[Johan_H]

Hi Jim,

In earlier versions of B1, properties were sometimes NULL in the database. Could you please test this version of your query:

IF ($[$40.2.0] = 'C' and 
       (ISNULL($[$136.2.0.1], 'N') = 'N' AND
        ISNULL($[$136.2.0.2], 'N') = 'N' AND
        ISNULL($[$136.2.0.3], 'N') = 'N' AND
        ISNULL($[$136.2.0.4], 'N') = 'N' AND
        ISNULL($[$136.2.0.5], 'N') = 'N' AND
        ISNULL($[$136.2.0.6], 'N') = 'N' AND
        ISNULL($[$136.2.0.7], 'N') = 'N' AND
        ISNULL($[$136.2.0.8], 'N') = 'N' AND
        ISNULL($[$136.2.0.9], 'N') = 'N' AND
        ISNULL($[$136.2.0.10], 'N') = 'N' AND
        ISNULL($[$136.2.0.11], 'N') = 'N' AND
        ISNULL($[$136.2.0.12], 'N') = 'N' AND
        ISNULL($[$136.2.0.13], 'N') = 'N' AND
        ISNULL($[$136.2.0.14], 'N') = 'N')
)
BEGIN
SELECT 'Not Filled' FOR BROWSE
END

Regards,

Johan

[jimbo1234]

Hi Johan,

Sorry, that didnt work either. But now, if it helps in the trouble shooting, if none of the properties are selected, the warning fires twice (if you select "No" which leads to a block of the event), but if you select one or more of them, the warning only fires once. Maybe something wrong with the setup? See screenshot attached.

[Johan_H]

Sorry, I am not familiar with this tool, but from the screenshot I understand that you can react to multiple outcomes.

How about checking for a positive result first and use ELSE to return 'Not filled'? You would have to separate the BP type parameter. Something like this:

IF $[$40.2.0] = 'C'
 BEGIN
  IF 'Y' IN  (ISNULL($[$136.2.0.1], 'N'),
              ISNULL($[$136.2.0.2], 'N'),
              ISNULL($[$136.2.0.3], 'N'),
              ISNULL($[$136.2.0.4], 'N'),
              ISNULL($[$136.2.0.5], 'N'),
              ISNULL($[$136.2.0.6], 'N'),
              ISNULL($[$136.2.0.7], 'N'),
              ISNULL($[$136.2.0.8], 'N'),
              ISNULL($[$136.2.0.9], 'N'),
              ISNULL($[$136.2.0.10], 'N'),
              ISNULL($[$136.2.0.11], 'N'),
              ISNULL($[$136.2.0.12], 'N'),
              ISNULL($[$136.2.0.13], 'N'),
              ISNULL($[$136.2.0.14], 'N'))
   BEGIN
    SELECT 0 /* at least one property was checked */
   END
  ELSE
   BEGIN
    SELECT 'Not Filled'
   END
 END

Regards,

Johan

[jimbo1234]

OK, i got that and changed it slightly and added another popup for testing under "Filled" as per code below. See the result...keeping in mind this is not on all business partners, only some:

When no properties were selected and i click "Update": "Not filled" warning appeared twice

When any properties were ticked: "Not Filled" Warning appeared once, then the "Filled" warning

IF $[$40.2.0] = 'C'
 BEGIN
  IF 'Y' IN  (ISNULL($[$136.2.0.1], 'N'),
              ISNULL($[$136.2.0.2], 'N'),
              ISNULL($[$136.2.0.3], 'N'),
              ISNULL($[$136.2.0.4], 'N'),
              ISNULL($[$136.2.0.5], 'N'),
              ISNULL($[$136.2.0.6], 'N'),
              ISNULL($[$136.2.0.7], 'N'),
              ISNULL($[$136.2.0.8], 'N'),
              ISNULL($[$136.2.0.9], 'N'),
              ISNULL($[$136.2.0.10], 'N'),
              ISNULL($[$136.2.0.11], 'N'),
              ISNULL($[$136.2.0.12], 'N'),
              ISNULL($[$136.2.0.13], 'N'),
              ISNULL($[$136.2.0.14], 'N'))
   BEGIN
    SELECT 'Filled' 
   END
  ELSE
   BEGIN
    SELECT 'Not Filled'
   END
 END

[Johan_H]

Is this the only validation taking place or are there others? Just wondering why you would get two warnings.