?
Solved

View filter formula based upon multi value column

Posted on 2007-03-29
9
Medium Priority
?
598 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 18818664
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
ID: 18823635
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
ID: 18823846
Whatever the solution, I completely agree with CRAK about doing the work in the document, not the view.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:pratigan
ID: 18844034
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
 
LVL 13

Expert Comment

by:CRAK
ID: 18846010
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
ID: 18846108
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
ID: 18846389
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 1000 total points
ID: 18849659
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
ID: 18852723
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 8 hours left to enroll

771 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