Solved

View filter formula based upon multi value column

Posted on 2007-03-29
9
588 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
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 250 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

772 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