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

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 ?

0
kmorrisroe
Asked:
kmorrisroe
  • 9
  • 7
  • 3
  • +1
2 Solutions
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 9
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now