display values in test box based on selection of value and conditions

[Sai78]

I have a condition when the user selects a value from the list box ..there is another test box the details need to be displayed... I have tried multiple ways but data not getting displayed as designed..could you please check the attached application and help.

Any insight would greatly be appreciated. Thanks in advance.

[edwin]

Your main problem is that there are cases where multiple STATUS and multiple TYPES.  for example, when you choose an identifier with multiple records.  there will only be one possible STATUS, but multiple occurrences of it so the expression STATUS=missing will fail.  it should be only(STATUS)=missing:

=if(GetPossibleCount(Identifier)=1 ,
if( only(Status)='Missing' , CONCAT(Missing_Field,',')

you will see this if you select identifier 'TVT-VNEZCVN2' as it only has one record where status = missing.   so you code returns the expected value.  otherwise it fails.

then your next issue is with the criteria Type = 'test type4' this will again fail if there are multiple instances of Type.

could it be you want to create a staright table instead so you can handle cases where multiple records are returned?

if you explain what you are trying to achieve, maybe someone can help.  hope this answers the question why your code is failing and leads you to a solution.

[Sai78]

Thanks for your reply...I have updated the application with meaningful values now

I have a list box with a unique identifier and a test box that should be displaying the reasons of why the identifier is listed on the list box.

My question is that the values in the test box display if the identifier has only one 'number/type' combination met  .. however if the identifier associated with multiple numbers/types and conditions test box doesn’t display any values by when user selects the identifier... 

My scenario here is irrespective of number of number/type combination and conditions  test box should display all the values accordingly.

if an identifier satisfy all conditions , the test box should display all values

Missing Values in ----

RelatedCEnd is ----

RelatedEEnd is ----

EStart_Date is  ----

=if(GetPossibleCount(Identifier)=1 ,
      if( Status='Missing' ,'Missing Values in '& CONCAT(Missing_Field,',')
          ,if (Type = 'Commercial' and rnk =1 and  RelatedCEnd_Date >=$(vToday)and RelatedCEnd_Date<=$(v30Days),'RelatedCEnd is '&  RelatedCEnd_Date
             ,if (Type = 'PA'   and rnk =1 and   RelatedEEnd_Date >=$(vToday) and RelatedEEnd_Date<=$(v30Days) ,'RelatedEEnd is '& RelatedEEnd_Date
                 ,if(Type = 'Commercial' and rnk =1 and   [claims] = '0'  ,'EStart_Date is' &  EStart_Date ) ))))

[edwin]

"My question is that the values in the test box display if the identifier has only one 'number/type' combination met  .. however if the identifier associated with multiple numbers/types and conditions test box doesn’t display any values by when user selects the identifier... "

i may not have communicated it properly.  we are both saying the same thing.  you should not use the expresstion STATUS= ... or TYPE=... as these fail if there are multiple rows.  the STATUS criteria is simple as you may have a unique status per identifier.  so you use

if(ONLY(STATUS)=...

now type is a different matter as you obviously have multiple types per identifier.  if (and this is a big if) TYPE for each identifier could be Missing and only one other, you can maybe get away with the following

=if(GetPossibleCount(Identifier)=1 ,
      if( only(Status)='Missing' ,'Missing Values in '& CONCAT(Missing_Field,',')
          ,if (only({<Type-={'Missing'}>}Type) = 'Commercial' and only(rnk) =1 and  max(RelatedCEnd_Date) >=$(vToday)and max(RelatedCEnd_Date)<=$(v30Days),'RelatedCEnd is '&  max(RelatedCEnd_Date )
             ,if (only({<Type-={'Missing'}>}Type) = 'PA'   and only(rnk) =1 and   max(RelatedEEnd_Date) >=$(vToday) and max(RelatedEEnd_Date)<=$(v30Days) ,'RelatedEEnd is '& max(RelatedEEnd_Date) 
                 ,if(only({<Type-={'Missing'}>}Type) = 'Commercial' and only(rnk) =1 and   only([claims]) = '0'  ,'EStart_Date is' &  max(EStart_Date) ) ))))

the change i did was placed aggregations around each field.  again you cannot use the expression FIELD=... if there are cases where there will be multiple instances or values for the field. hence the ONLY and MAX (im guessing business requirements here)

[Sai78]

Thank you for the insight!

The updated logic also didn't get displayed when identifiers have multiple types /numbers associated with

an example...if the user selects identifier 'TVT-S2qiptk0 ' displays without any issues as this identifier has only one number and type associated with, However, if the user selects identifier s 'TVT-R65X0tX0'  or 'TVT-R7KjljK0' or 'TVT-Rd1bmvh0 ' identifier values are not getting displayed.. My requirement is to list out all the conditions that attention needed...

Could you help with this as to how to achieve this?

[Kushal_Chawda]

@Sai78  what is expected output for  TVT-RPQ5Tgh0 ?

[edwin · Accepted solution]

these are new test cases.  the expression 

only({<Type-={'Missing'}>}Type) =

assumes that each identifier can only either have type missing or one other.  for identifier TVT-R7KjljK0, it has 3 types, missing, PA, and commercial.  this scenario is not addressed by the code.  you need to decide what you do in scenarios like these?  is it commercial?  is it PA? you need to go back to your business rule.

i would suggest you organize your logic in some structure like :

if ID HAS commercial then
       if max of rnk =.... and max(RelEndDate)>=...
      else if max(rnk)=... and max(RelEndDate)>=...
else if ID HAS PA then
      if max of Rnk =....

now to code the phrase if ID HAS commercial you can  use this:

only({<Type-={'Missing','PA'}>}Type) = 'Commercial'

this is for if ID HAS PA:

only({<Type-={'Missing','Commercial'}>}Type) = 'PA'

will all your selections result in an entry in your text box?  answer is no.  bec for some you also need to check rnk, relatedDate, and claims.  if neither of the conditions are met, you get NULL.