Multi-value fields in views

[WalWeb]

Can anyone advise if there's any way to do what I'm trying to do?

I have a client customer contract database, and for every customer I track contract expiry dates for as many as five services in individual customer records. As well as what's visible, I store the expiry dates in a hidden computed multi-value field and can quite easily build a view that gives me the customer identifier information appearing as many as five times under separate categorized dates (stored as ISO formatted YYYYMMDD values so I get a sensible sequence out of them), by checking the "Show multiple values as separate entries" box.

What I can't seem to do reliably is to identify WHICH contract is expiring on any given line -- short of opening the customer record and eyeballing it. A simple variant of what I'm trying is to attempt to break the individual date sub-fields -- the multiple values -- back down into their components, in order to (for example) sort on the stored value as a hidden column, within which you'd categorize the year and month, only with the month translated into a name rather than a number ... such that (for example) 2009 January displays as it should, ahead of 2009 February.

Any advice gratefully accepted.

 

[pmonett]

I think you might need to add another hidden, multi-value date field that contains the time left between the currently stored date list and the expiry date you have.
If the difference is negative, you can display an expiry icon, or red flag or whatever.

What do you think ?

As for displaying dates with month names, you still have to sort on the pure date value first, even if you hide it.

Pascal.

I've got nothing to hide, and I'd very much like to keep that away from prying eyes.

 

[WalWeb]

Thanks for that, Pascal. Problem is that there's no way to keep more than one multi-value field sync'd with another's values. And I've found that even within a single multi-value field, if I tack on to each value within that field a code representing which type of service contract is expiring, Notes gets lost in trying to translate those codes back into a meaningful description, and seems to stick with the last one it finds.

Thus if I have a m-v field containing (for example)

200908X;201102Y;201305Z

and I disassemble each value for use as a "YYYY/MM : Desc" category using a column formula something like this:

a := MultiValueField;
b := @Text(@Left(a;4));
c := @Text(Middle(a;4;2));
d := @Right(a; 1);
e := @If(d="X"; "DescA"; d="Y"; "DescB"; d = "Z"; "DescC"; "DescD");
b + "/" + c + " : " + e

... then I'll end up with categories of

2009/08 : DescC
2011/02 : DescC
2013/05 : DescC

What I've found DOES work is tacking on the value in its already-expanded form (disk's cheap!), as

200808DescA;201102DescB;201305DescC;

... but only if what's tacked on is all the same length, because if it's not, Notes will pull in parts of the date component as well to make up the maximum subfield length for each value.

It means you end up with categories like what's shown, which works ... but nowhere near as elegantly, or as usably, as (for example) categories that would read

2009 August : DescA
2011 February : DescB
2013 May : DescC

What would be best, of course, would be categories and SUB-categories like

2009
August
DescA

2011
February
DescB (etc)

... you get the idea; I've used it elsewhere (with SINGLE values per record) and it works brilliantly. Unfortunately, that really sends Notes nuts if you try to do it with a multi-value field, because what you end up with if you try to reference the multi-value field in more than one column is each value appearing as a subcategory of every category where the record qualifies; ie, three times for 2009, three time for 2011, three times for 2013, and within each major category, it recurs for each sub-category.

Likewise, with single-value fields, I've had success with sorting on a hidden column, and within that sequence, displaying a translated, categorized version of the same data, as shown above; but again, if the field you're referencing for both such columns is a multi-value field, it simply doesn't work.

 

[pmonett]

Try using @For :

WkList := "";
WkMax := @Elements(MultiValueField);
@For(iter := 1;iter <= WkMax; iter := iter + 1;
	cWkVar := MultiValueField[iter];
    b := @Text(@Left(cWkVar;4));
    c := @Text(Middle(cWkVar;4;2));
    d := @Right(cWkVar; 1);
    e := @If(d="X"; "DescA"; d="Y"; "DescB"; d = "Z"; "DescC"; "DescD");
	WkList := b + "/" + c + " : " + e;
);
WkList

Pascal.

I've got nothing to hide, and I'd very much like to keep that away from prying eyes.