• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

View filter formula based upon multi value column

hello All,
I have an interesting request.
I have a form that has all unique field values, however I allow multiple entires of a certain type.  Such as, I have a small table in the form that allows for element names (elm1,elm2,elm3,elm4, etc) under 1 form entry clasified by say 1 pkg ID.  So lets say pkdID 1234 includes 6 elements, elm1 - elm6.  I have a view that I've created by element, I have indicated multiple values new line and separate entries as new line.  So I'm getting 6 different rows for 1 pkg.  Exactly what I want.  Now I want to filter the element by a particular value.  I have no idea how to code the view formula to filter on element values of say show me a new line for every elm that is equal to "YES" only.
Here is the element column in the view.
@Trim(CMprog_elem1:
CMprog_elem2:
CMprog_elem3:
CMprog_elem4:
CMprog_elem5:
CMprog_elem6:
CMprog_elem7:
CMprog_elem8:
CMprog_elem9:
CMprog_elem10)
this will not show me an entry that does not have an element value.
Now I only want separate rows for elements that equal "YES"
Hope this makes sense.
Thanks !
Paul
0
pratigan
Asked:
pratigan
  • 4
  • 3
  • 2
1 Solution
 
Bill-HansonCommented:
Are you saying that a CMprog_elemX element should only appear if another field (elmX) equals "Yes"?  If so, try this:

@Trim(
@If(elm1 = "Yes"; CMprog_elem1; ""):
@If(elm2 = "Yes"; CMprog_elem2; ""):
@If(elm3 = "Yes"; CMprog_elem3; ""):
@If(elm4 = "Yes"; CMprog_elem4; ""):
@If(elm5 = "Yes"; CMprog_elem5; ""):
@If(elm6 = "Yes"; CMprog_elem6; ""):
@If(elm7 = "Yes"; CMprog_elem7; ""):
@If(elm8 = "Yes"; CMprog_elem8; ""):
@If(elm9 = "Yes"; CMprog_elem9; ""):
@If(elm10 = "Yes"; CMprog_elem10; "")
)

0
 
CRAKCommented:
If I understand correctly, you're only interested in the items in your trimmed list, containing "YES" (so you end up having a number of "YES"-values).

Let's call your list LST0:
LST0:=@Trim(....);

Create a copy of that list with only those values that you need removed from LST0. I.e. remove all entries "YES" from LST0:
LST1:=@Trim(@Replace(LST0; "YES"; ""));

Now remove all entries in LST1 from LST0:
@Trim(@Replace(LST0; LST1; ""))

PS:
Your application will perform better if you handle computations like these on the form: just add another field to do the computation, and use/show it in the view!
0
 
Bill-HansonCommented:
Whatever the solution, I completely agree with CRAK about doing the work in the document, not the view.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
pratiganAuthor Commented:
I understand your indication to do the calculations in the form...... I would agree with you if this was the only situation.  Here's what the whole picture is.
within my form I have a heading section that contains status, pkgID, dates etc.  Individual single value fields.  Further down I have a table that contains multiple columns that lists program names, program types, etc.  This table contains pgmname1 - 10 column, pgm type1 -10 and other columns.  I created a view that lists the pkgIDs by date, no problem.  Then I needed a view to list a row per pgmname which needed the list of multiple fields by pgmname like above.  Now I want to list the pgmnames by type but just whent he type = PLOG.  So a row for each pgmname that is associated with pgmname type PLOG.
when I created a view listing all elm1 - 10 and then new line for each, that worked exactly how I was hoping.  Now I want to list elm1 - 10, but only the rows where the type = "PLOG"
hope this clears things up.
Thx.
0
 
CRAKCommented:
Similar to my example above:
Create a list of all names, create a list of all types (same order).
Concatenate the two lists using a separator. This is your "basic" list.
Build another list concatenate where you apply a modification to the elements that you want to KEEP. This is your "deletion list".
Delete all elements occurring in your "deletion" list from your "basic".

What happens is that all unmodified elements are replaced with an empty string, but the types modified earlier do not occur in the basic list, so the original entries in the "basic" list will remain!

Final step is to show only the fragments left of the separator (names; you know the type...).

In code:

LstName:=pgmname1:pgmname2:pgmname3:....(etc)...;
LstType:=pgmtype1:pgmtype2:pgmtype3:...(etc)...;

Lst0:=LstName + "~" + LstType;
Lst1:=LstName + "~" + @Replace(LstType; "PLOG"; "deleted");

@Trim(@Left(@Replace(LST0; LST1; ""); "~")
0
 
pratiganAuthor Commented:
ok.... I actaully have 5 columns in my view that contain multi values from the form table.  I have pgmname, type,platform,number,etc.  so I want only the rows that have a type of "PLOG" containing that particluar row on the table for the other multi fields.
I assume I would apply the same principle as you indicate. Create a merged list for each ???
Here is an example of what I have:
@Trim(CMprog_elem1:
CMprog_elem2:
CMprog_elem3:
CMprog_elem4:
CMprog_elem5:
CMprog_elem6:
CMprog_elem7:
CMprog_elem8:
CMprog_elem9:
CMprog_elem10)
I have this same type of code for all 5 columns, except of course the field names are slightly different.
0
 
pratiganAuthor Commented:
I have an update.  I have been playing with the @TRIM function while thinking about your recommendations and I have an interesting situation that I believe explains why I need to do it your way by grouping them.  I have this formula:
temp1 := @If(CMplog_X1 = "PLOG";CMplog_X1;"");
temp2 := @If(CMplog_X2 = "PLOG";CMplog_X2;"");
temp3 := @If(CMplog_X3 = "PLOG";CMplog_X3;"");
temp4 := @If(CMplog_X4 = "PLOG";CMplog_X4;"");
temp5 := @If(CMplog_X5 = "PLOG";CMplog_X5;"");
temp5 := @If(CMplog_X6 = "PLOG";CMplog_X6;"");
temp7 := @If(CMplog_X7 = "PLOG";CMplog_X7;"");
temp8 := @If(CMplog_X8 = "PLOG";CMplog_X8;"");
temp9 := @If(CMplog_X9 = "PLOG";CMplog_X9;"");
temp10 := @If(CMplog_X10 = "PLOG";CMplog_X10;"");
This works for PKGID docs that have both PLOG and other types, however I am getting blanks in the cmplog field and it is still showing as a separate row in the view if the table has no PLOG but something else.  I believe this is because the other 4 columns contain the @Trim() for the multiple fields only, not the logic looking to exclude type other than PLOG, thereby having to group them ?????
0
 
CRAKCommented:
The issue when none of the type fields hold PLOG will probably occur in my solution as well: if all strings get removed from the list, you'll still have "" as result unfortunately. But doing that computation in the form will at least offer you something to filter on in your SELECT formula!
However, if you need more filters (not only PLOG, but dozens of others), you may need quite a number of fields.

With that in mind, and your desire have a total of 5 columns involved, I think I'd add just 2 computed fields to the form: one to store LstType (from my example above), and one to list those columns as concatenated strings:

LstCol1:=pgmtype1:pgmtype2:pgmtype3:...(etc)...;
LstCol2:=...;
LstCol3:=...;

ListName:=LstCol1+"~"+LstCol2+"~"+LstCol3... (that would be the 2nd field)


In a view that requires filtering on PLOG, you could check if LstType contains just that value. If not: don't show the document, or it'll end up as an empty entry.


The @Replace trick can now be performed in the view's columns.
The @Left, however, won't prove of much use when working with multiple columns. You're better off using @Word instead!

Next concern is if you want to show those 5 columns (fields) in different view columns. I'm afraid that cannot be done!

0
 
pratiganAuthor Commented:
Hello CRAK,
Thank you much for the feedback.  I worked through the problem by tying every column that had multi fields from the table (5) columns in the view, tying them back to the type = "PLOG".  Once I did that it eliminated all but those pkgids that did not have any PLOG values.  I then went out to the view level and add the logic to the view formula stating that type = "PLOG" for at least one of the fields in the table.
Once I did this it worked like a charm.  If you would like the detailed logic for each field and the view formula, let me know and I will post.
thank you for all your help.  I have granted you the points for your input and probable working answer.
Thanks Again !!
Paul
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now