Solved

View filter formula based upon multi value column

Posted on 2007-03-29
9
582 Views
Last Modified: 2013-12-18
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
Comment
Question by:pratigan
  • 4
  • 3
  • 2
9 Comments
 
LVL 22

Expert Comment

by:Bill-Hanson
Comment Utility
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
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
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
 
LVL 22

Expert Comment

by:Bill-Hanson
Comment Utility
Whatever the solution, I completely agree with CRAK about doing the work in the document, not the view.
0
 
LVL 4

Author Comment

by:pratigan
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:CRAK
Comment Utility
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
 
LVL 4

Author Comment

by:pratigan
Comment Utility
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
 
LVL 4

Author Comment

by:pratigan
Comment Utility
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
 
LVL 13

Accepted Solution

by:
CRAK earned 250 total points
Comment Utility
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
 
LVL 4

Author Comment

by:pratigan
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now