Creating a Column View

I have created a form for ordering products,

The  form has 10 products fields starting with product1, product2 and so on. each product has a status - Intentended Install Date - Confirmed Install Date etc.

I want to be able to create a view so that product fields appear on the left hand side instead of across the top. I would basically want the view to look like this:
                  > Doc1                              Intended Dispatch Date     Confirmed Dispatch Date
                                  Product1(ComboFields)
                                  Product2
                                  Product3

                   > Doc2                              Intended Dispatch Date     Confirmed Dispatch Date
                                  Product1(ComboFields)
                                  Product2
                                  Product3

Any Ideas ?

kmorrisroeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sjef BosmanGroupware ConsultantCommented:
I assume you have Product1..Product10, Status1..Status10, etc. If the status isn't to be shown, you could have one column with the formula:
    @Trim(Product1:Product2:...:Product10)
In the view, you have to specify that you need 10 lines per document. You can also select Shrink to content if there are less products.

Is that what you're looking for??
0
kmorrisroeAuthor Commented:
No the status need to be shown, for example I need to find all the products with an intended dispatch in the next 7 days, There could be 7 products in each document and I need for the view to show the dates also.

How would notes deal with this ?
0
SysExpertCommented:
I hate to say it, but it really should be set up to have only one product line per doc. Then the views would be simple.

trying to extract the info into a view the way it is organized now may not be possible.

I hope this helps !
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Sjef BosmanGroupware ConsultantCommented:
In a Notes view, each line is a document. There are cases that a document can show up twise in a view, but each time with the same data. Presenting the same document twice with different data isn't going to work. If you absolutely have to make this work, you could put the product-related information in a separate (response) document. That's far from easy, because you need to refresh these product documents whenever the parent document gets updated. Very little else is possible. The only other option left is to create a categorized view, with the product-related info in the first (categorized) column. The formula:
    products:= Product1:Product2:...:Product10;
    statuses:= Status1:Status2:...:Status10;
    @Trim(@Replace(products + "\\" + statuses; "\\"; ""))
0
kmorrisroeAuthor Commented:
Could I merge the fields within the doc, so I would have 10 hidden computed fields, they would be product + intended installed date & confirmed install date.

How would I then display in a view ?



0
Sjef BosmanGroupware ConsultantCommented:
That's not necessary, but it could be a practical thing to do, since it removes computations from the view to the form. On the other hand, it creates additional fields and therefore consumes disk space (what the heck...). The problem doesn't change though. You could put the formula I gave above in some other column in the view, non-categorized, and with a different separator. It just isn't really possible to do this without (a lot of) additional programming.
0
marilyngCommented:
I so love puzzles... see if this works for you...

On your form, create a computed field for all your entries:  I call it VC, multi-value, separate entries with new line, new line (that is, separate values when a new line is encountered, and show as a new line)

Here's the formula I used:  (IDD = Intended Dispatch Date, CDD = Confirmed Dispatch Date)
P1:=@If(Product_1="";"";Product_1 + "|" + @Text(IDD_1;"D0S0") + "|" + @Text(CDD_1;"D0S0" ));
P2:=@If(Product_2="";"";Product_2 + "|" + @Text(IDD_2;"D0S0") + "|" + @Text(CDD_2;"D0S0"));
P3:=@If(Product_3="";"";Product_3 + "|" + @Text(IDD_3;"D0S0") + "|" + @Text(CDD_3;"D0S0"));
P4:=@If(Product_4="";"";Product_4 + "|" + @Text(IDD_4;"D0S0") + "|" + @Text(CDD_4;"D0S0"));
P5:=@If(Product_5="";"";Product_5 + "|" + @Text(IDD_5;"D0S0") + "|" + @Text(CDD_5;"D0S0"));
P6:=@If(Product_6="";"";Product_6 + "|" + @Text(IDD_6;"D0S0") + "|" + @Text(CDD_6;"D0S0"));
P7:=@If(Product_7="";"";Product_7 + "|" + @Text(IDD_7;"D0S0") + "|" + @Text(CDD_7;"D0S0"));
P8:=@If(Product_8="";"";Product_8 + "|" + @Text(IDD_8;"D0S0") + "|" + @Text(CDD_8;"D0S0"));
P9:=@If(Product_9="";"";Product_9 + "|" + @Text(IDD_9;"D0S0") + "|" + @Text(CDD_9;"D0S0"));
P10:=@If(Product_10="";"";Product_10 + "|" + @Text(IDD_10;"D0S0") + "|" + @Text(CDD_10;"D0S0"));
@Trim(P1:P2:P3:P5:P6:P7:P8:P9:P10)

View:
Do your select criteria -
Col1 = Document (doc1, doc2) Sorted Ascending,Categorized
Col2 = @Word(VC;"|";1), Sorted Ascending, Show multiple values as separate entries.
Col3= @Word(VC;"|";2), No Sort, Show multiple values as separate entries.
Col4=  @Word(VC;"|";3), No Sort, Show multiple values as separate entries.

To test to make sure that the values lined up correctly, I did this:
Product_1="Product 1"  IDD:=03/01/2006 CDD:=04/01/2006
Product_2="Product 2"  IDD:=03/02/2006 CDD:=04/02/2006
Product_3="Product 3"  IDD:=03/03/2006 CDD:=04/03/2006

So that the product had a line number that was the same across the DAY in the date fields

Document = "Doc1"

Then copied the document 4-5 times, and changed the Document# to the next one.

Hope this helps..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sjef BosmanGroupware ConsultantCommented:
New puzzle for you, Marilyn: can you do this with list-operations?
0
marilyngCommented:
Not following?  Whatcha mean?
0
Sjef BosmanGroupware ConsultantCommented:
You have 10 @If's in your code. I think it is possible to obtain the same result but without the @If's, in order to remove the 10 lines of similar code.
0
marilyngCommented:
Yeah, but not using pair-wise, because there's no way to see if product is empty in order to construct the rest of the line.

For example:
Let's suppose I have a document with two product fields entered:  
Product _1="Cars"         1/1/2006           1/1/2007
Product_3="Boats"         3/1/2006           3/1/2007
-----------------
PAIRWISE FORMULA:  Here's the pair-wise formula.............

nums:="1":"2":"3":"4":"5":"6":"7":"8":"9":"10";
ProdList:=@GetField("Product_" + Nums);
IDDList:="|" + @Text(@GetField("IDD_" + nums))+ "|";
CDDList:="|" + @Text(@GetField("CDD_" + nums));
ProdList+IDDList+CDDList

Will correctly return:
           Cars|1/1/2006|1/1/2007
           Boats|3/1/2006|3/1/2007

------
But misstep: (where someone does the hidewhen, and then forgets to add a translation formula to remove data in the rest of the rows)
Product _1="Cars"         1/1/2006           1/1/2007
Product_2=  ""               2/1/2006           2/1/2007
Product_3="Boats"         3/1/2006           3/1/2007

Would incorrectly return:
          Cars|1/1/2006|1/1/2007
          2/1/2006|2/1/2007 <<    This would be wrong
          Boats|3/1/2006|3/1/2007
---
Similarly this incorrect entry:
Product _1="Cars"         1/1/2006           1/1/2007
Product_2=  ""               2/1/2006          
Product_3="Boats"         3/1/2006           3/1/2007

Does this:
          Cars|1/1/2006|ERROR...
          2/1/2006|ERROR...
          Boats|3/1/2006|ERROR...
----------------------------------------------------------------------------------------------------------
ANOTHER FORMULA SOLUTION: (And a bit better than the previous one I posted)

This does it in three @if's, is much harder for the user to read, BUT does trap the incorrect date values.

nums:="1":"2":"3":"4":"5":"6":"7":"8":"9":"10";
ProdList:="Product_" + Nums;
IDDList:="IDD_" + nums;
CDDList:="CDD_" + nums;
List:="";
@For(n := 1; n <= @Elements(nums); n := n + 1;
  NewProd:=@GetField(ProdList[n]);
  IDD:=@If(@GetField(IDDList[n]) !="";"|" + @Text(@GetField(IDDList[n]);"D0S0");"|No IDD Date");
  CDD:=@If(@GetField(CDDList[n]) !="";"|" + @Text(@GetField(CDDList[n]);"D0S0");"|No CDD Date");
  FullList:=NewProd + IDD + CDD;
  List:=@If(newProd="";List;List:FullList));
List


------------------

Gauntlet is down.. your turn ;)
0
Sjef BosmanGroupware ConsultantCommented:
> ... there's no way to see if product is empty...
Isn't there?? How about this:

    p:= Product1:Product2:Product3...:Product10;
    d:= @Text(Date1:Date2:Date3:...:Date10);

    pd:= p + "|" + d;
    x:= "|"+@Right("~" + pd; "~|");
    @Trim(@Replace(pd; x; ""))
0
marilyngCommented:
Nice try!  Did you test?

what happens when:
p:= product1:Product2:Product3;
d:=@Text(Date1:Date2:Date3);

Where Product1="Apples", Product2="",Product3="Pears"
and Date1="1", Date2="2",Date3="3"

You get, I think:  
pd:=Apples:Pears;
d:=1:2:3;
pd:=Apples|1:Pears|2:Pears|3;

That was what I meant when there's no way to test for the empty value without the @if before your pair-wise concatenation, unless you are VERY good about removing associated field values when the main row field is empty. That is, if product2="", then date2 & date3 Translation formula should change date2 & 3 = "".

Thwump... still down on your side.. ;)

0
Sjef BosmanGroupware ConsultantCommented:
> You get, I think...
Well, better think again.

    Product1:= "prod1";
    Product2:= "";
    Product3:= "prod3";

    Date1:= [01-01-2002];
    Date2:= [01-01-2003];
    Date3:= [01-01-2004];

    p:= Product1:Product2:Product3;
    d:= @Text(Date1:Date2:Date3);
   
    pd:= p + "|" + d;
    x:= "|"+@Right("~" + pd; "~|");
    @Trim(@Replace(pd; x; ""))

produces
    prod1|01-01-2002; prod3|01-01-2004

Tested. Of course... :-D
0
marilyngCommented:
SO CLOSE! You also need to test for blank dates when a product is filled in ;)
(Also, there are two dates, one product)

So, while your solution works for this:
"Cars"    1/1/2006   1/1/2007
---         ---             ----
"Boats"   3/1/2006   3/1/2007

It does not work for this:

"Cars"    1/1/2006   1/1/2007
"Chairs"        ---             ----
"Boats"   3/1/2006   3/1/2007

Or this:
"Cars"    1/1/2006   1/1/2007
"Chairs"   2/1/2006             ----
"Boats"   3/1/2006   3/1/2007


Smile - I tried that before I posted the one that works ;)

---------------------------------------------------------------------------
THIS IS THE ONE THAT WORKS:

This does it in three @if's, is much harder for the user to read, BUT does trap the incorrect date values.

nums:="1":"2":"3":"4":"5":"6":"7":"8":"9":"10";
ProdList:="Product_" + Nums;
IDDList:="IDD_" + nums;
CDDList:="CDD_" + nums;
List:="";
@For(n := 1; n <= @Elements(nums); n := n + 1;
  NewProd:=@GetField(ProdList[n]);
  IDD:=@If(@GetField(IDDList[n]) !="";"|" + @Text(@GetField(IDDList[n]);"D0S0");"|No IDD Date");
  CDD:=@If(@GetField(CDDList[n]) !="";"|" + @Text(@GetField(CDDList[n]);"D0S0");"|No CDD Date");
  FullList:=NewProd + IDD + CDD;
  List:=@If(newProd="";List;List:FullList));
List

0
Sjef BosmanGroupware ConsultantCommented:
Ok, all right, you might win, but would you permit a small change?

    Product1:= "prod1";
    Product2:= "";
    Product3:= "prod3";

    Date1:= "";
    Date2:= [01-01-2003];
    Date3:= [01-01-2004];

    p:= Product1:Product2:Product3;
    d:= @Text(Date1):@Text(Date2):@Text(Date3);
   
    pd:= p + "|" + d;
    x:= "|"+@Right("~" + pd; "~|");
    @Trim(@Replace(pd; x; ""))

Your move...
0
marilyngCommented:
x:= "|"+@Right("~" + pd; "~|");  << Still very clever!  Great fun this!
0
Sjef BosmanGroupware ConsultantCommented:
And if someone says "Duh, I can use the ~ in my strings..." my answer would be "Fine, then take ~!#@!~~#@ as separator".

Indeed, lists are fun and very powerful, but it takes some time to twist your brains to understand the implicit For-loop inside them.
0
marilyngCommented:
Yeah, and then you have to make sure you don't create an infinite loop  with the lists and run out of memory... (did this once, ahem)

At any rate, we're having great fun but as of yet no replies from kmorrisoe... maybe abandoned the question?
0
kmorrisroeAuthor Commented:
This solution really works a treat. You are both a credit to the site. Thanks a Million, Sorry for the Delay been on my Hols.



0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.