Multiple values in view issue

Just been thinking about this and wonder if this would work:

I'm already using programmed column names in columns further across to spit down total amount into notes and coins required to make it up, has anyone tried using @subset() referring to a programmed column name which is a categorised column and therefore matching it against the individual entry.... will try this out later but if you have already experienced this it would save me testing the impossible.

thanks

Steve

Hi dragon-it,
i.e. here's how you determine which docs are shown in the view (view selection formula for docs whose fieldA date is in last 30 days):

Dat := @Adjust( @TextToTime( "Today" ); 0; 0; -30; 0; 0; 0 );
FieldADate := @Date( FieldAPaidDate );
SELECT Form = "frmName" & FieldADate > Dat

Does this help? I didn't understand your question very well.
Marko

Gave column1 a name and referred to it another column and of course it gets the same mutli-value as column1.  Oh well, worth a try.

Any ideas?

Steve

OK, sorry if it wasn;t clear.  There are two monetary awards given out FieldA and FieldB which are show in the view.  They have independent paid dates which are made multi-value and categorised in column 1 to give total amount paid out for either FieldA or FieldB on one day.

i.e.

column 1: FieldAPaiddate:FieldBPaiddate  categorised
column 2 @If(@IsTime(FieldAPaiddate);FieldA))
column 3 @If(@IsTime(FieldBPaiddate);FieldB))

This is simplified of course, date is formatted into yyyy \ mmmm \ d etc. and various other columns but that is the bit I'm dealing with here/

What I really need is something in column 2 that can say

col2. If the category in column 1 showing on this particular row equals the FieldAPaidDate then show FieldA
col3. If the category in column 1 showing on this particular row equals the FieldBPaidDate then show FieldB

otherwise it's two views I guess.

hope thats clearer!

Steve

dragon-it,
yes it's clearer,
why not test like this:
@If( fieldAPaidDate != "";
        fieldA;
        @if( fieldBPaidDate != ""; fieldB; "" )
    )

you don't need to test which is displayed by using column names (as I see the problem).

Hope this helps,
Marko

The trouble is bcause this is categorised then you will get smething like this with these values if both are paid (it works of course if only one is paid):

FieldA    100
FieldB    200
FieldApaidDate    1/1/2006
FieldBPaidDate    2/2/2006
All fields are on same document...

1/2/2006     100        200
                  100        200
2/2/2006     100        200
                  100        200

whereas of course I want

1/2/2006     100         0
                  100
2/2/2006     0            200
                                200

Where the numbers next to the dates are totals related to the category.  The way it is in the first example it has the effect of showing payments on the wrong day and doubling up the payments in the totals...

Effectively I want to know what the category on the current line in column 1 is otherwise I can't know which column entry to show.  Think I may be flogging a dead horse as have been using Notes for >10 years!

Steve
 

IMHO this is impossible. Categorized views are great, but the real drawback is that you don't know what's displayed in a categorized column. The value you get is always the entire list of internal values, but to the user only one value is displayed. I'm sorry...

Just some ideas:
- combine it with the category itself, like
    Category + "\\" + @Text(somevalue)
- create multiple sub-documents (responses), one for each categorized value.

You're right, it sucks...

I know, I usually can't find recent questions at EE for their indexing is sometimes weeks behind. I don't keep a history myself. I would have liked to have all questions in a Notes database automatically, but I don't know how to log in using web.nsf. There must be some cookie that I need in that database, but the native Notes browser can't handle it. And I'm just too darn lazy to start classifying old questions myself...

See I knew I'd get some ideas here :-)  Unfortunately there is no chance of creating responses to hold any of the data ( that would be just too messy ) and using \ to drop down extra sub-categories would be Ok but I need totals too.

I'll have a plough through that other Q but I do think it's just not going to work when categorising by date like this.. I don't think I'm going to get all the totals in the right places etc... will try and report back anyway.  If not it's two views, one for FieldA, one for FieldB I guess.  Don't like defeat!

thanks for your help guys.

Steve

Excellent stuff.  Think you've cracked it with that one.

Col1.  Hidden column, no sort etc.  Programmatic name of "$Column1"

Date1:= BI_CommitteeRewardPaidDate;
Date2:= BI_ImplementationRewardPaidDate;

Val1:= BI_CommitteeReward;
Val2:= BI_ImplementationReward;

d:= @Text(Date1):@Text(Date2);
v:= @Text(Val1):@Text(Val2);
t:="ECM":"Imp";

    dvt:= d + "|" + v + "|" + t;
    x:= "|"+@Right("~" + dvt; "~|");
    @Trim(@Replace(dvt; x; ""))

Col2:  @TextToTime(@Word($Column1;"|";1))  sorted, categorised, and therefore forced 'multiple values as seperate entries'
Col3: @TextToNumber(@Word($Column1;"|";2)):  multiple values as seperate entries.
Col4: @Word($Column1;"|";3)

Now it would be *nice* if I could get two columns out of this so I can get two totals as originally suggested above but this is already a lot better than I thought I'd get to today...

any ideas on that bit?

thanks

Steve

Not bad... Not bad at all. But, just an example (I'll verify this in Notes some day, so I'm just thinking out loud now):

    Date1:= [1-1-2001];
    Date2:= [2-2-2002];
    Val1:= 10000;
    Val2:= 20000;
so
    d:= "Jan 1 2001":"Feb 2 2002";
    v:= "10000":"20000";
    t:="ECM":"Imp";
    dvt:= "Jan 1 2001|10000|ECM":"Feb 2 2002|20000|Imp";
and, since there are no empties:
    Col1 = "Jan 1 2001|10000|ECM":"Feb 2 2002|20000|Imp"

If I still got it right, $Column1 should always contain
    "Jan 1 2001|10000|ECM":"Feb 2 2002|20000|Imp"
and Col2 should be displaying values like
    "Jan 1 2001|10000|ECM"
    "Feb 2 2002|20000|Imp"
but it is still ONE line, so I assume this doesn't work. I'll be checking this, definitely. It cannot work. It shouldn't work. But if it DOES work..

Tested. Hmm... It seems to work, but... the values under the hood are still multi-value, just as I though. I tried this in the two-column version:
    x:= @TextToNumber(@Word(@Trim(@Left($Column1;"ECM"));"|";2));
    @If(@IsError(x); ""; x)
and
    x:= @TextToNumber(@Word(@Trim(@Left($Column1;"Imp"));"|";2));
    @If(@IsError(x); ""; x)
but the view stils displays two different values on each line.

The funny part is, with the original formula, when Totals is selected for the column, the correct total of 30000 is displayed.

I'm absolutely stunned.

You see, I knew it!  Last time sjef said it couldn't be done... then I started noodling, then we just had a good time coming up with variations on the theme..

Dragon-it Glad you got some ideas out of it.. if I get some time tonight I'll play around, too.

Heh never really used the 'show multiple values as seperate entries' option much but it is working quite nicely.  There are around 40 columns after those above breaking amounts down into notes and coins using programmatic names of the previous column to keep the formulas short and once they each had 'show multiple values as seperate entries' the values and therefore the totals follow the correct category.

Now just tried this: (testing at the moment) and seem to be able to get my extra column but struggling to get rid of the blank category that is produced when only one of two columns is required.

col1:
Date1:=@If(@IsTime(BI_ImplementationRewardPaidDate);@Text(BI_ImplementationRewardPaidDate);"");
Date2:=@If(@IsTime(BI_CommitteeRewardPaidDate);@Text(BI_CommitteeRewardPaidDate);"");
@TextToTime(Date1:date2)

col2:
@TextToNumber("0":@If(@IsNumber(BI_CommitteeReward);@Text(BI_CommitteeReward);"0"))

col3:
@TextToNumber(@If(@IsNumber(BI_ImplementationReward);@Text( BI_ImplementationReward);"0"):"0")


Thanks for all the pointers!!

Steve

Started typing that reply before all your bits inbetween.  Will keep this open until I've played around in the morning with it too but think I've got as far as I can now!

thanks for all the help

Steve

Just an idea: what if you try to erase the value that's not supposed to be in a column WITHOUT shortening the list of values?

The only way I could see to get Notes to choose to show in the right column using these formulas is to have a fixed no. of multi-values.... as of course we are talking the same document here so despite what displays in the view as we've proved above the actual column value is always multi-value.

Hmm now you;ve got me thinking with hiding in the columns too, maybe I could check using @Elements in the two value columns and if @elements is 1 then check which one it is...

Col1:
(lots of date mangling code leaving one or two dates multi-value)  Programmatic name. FinYear1

Col2:
REM "If only one date is in col1 and this date is the ECM date then show ECM reward otherwise show multi-value to line up with both ECM & Imp rewards";
@If(@Elements(FinYear1)=1 & @IsTime(BI_CommitteeRewardPaidDate); BI_CommitteeReward;
@TextToNumber("0":@If(@IsNumber(BI_CommitteeReward);@Text(BI_CommitteeReward);"0")))

Col3:
REM "If only one date is in col1 and this date is the Imp date then show Imp reward otherwise show multi-value to line up with both ECM & Imp rewards";
@If(@Elements(FinYear1)=1 & @IsTime(BI_ImplementationRewardPaidDate); BI_ImplementationReward;
@TextToNumber(@If(@IsNumber(BI_ImplementationReward);@Text( BI_ImplementationReward);"0"):"0"))

Seems to work :-)  All in the right place and no extra category.

Steve

0:17 over here. I think I'm going to have a look at the ceiling of my bedroom...

Only an hour behind too, time to finish on this one for the night now I think.  Appreciate all the help, wouldn't have thought of doing it this way without your suggestions so points to Sjef I think though Marilyng pointed me at the first good bit :-)

500 points doesn't really do a problem like this justice in these days of points inflation.

Are "points for " questions still allowed here to give you something too marilyng?

thanks

Steve

Grin.. as far as anyone knows from your accepted answer, the solution is still impossible.  Which it is not.  The point of awarding points is so that people trying to find a solution similar to yours are guided in the right direction.

I have no problem with sjef working with you (once he got over the "it's impossible" response that you accepted as the answer).   I didn't have the time to spend with you.

A split would have done more justice to Marilyn's contribution. I couldn't find the link she produced. And some credits to yourself too, I'd never have continued without your persistence (and I just wanted to prove everyone wrong :-S ). It's funny, but everybody's right here: you CAN have separate columns in a view based on multi-value fields, nevertheless the first column will ALWAYS be multi-value. I wouldn't know what happens if you base further columns on the derived columns $Column3 and $Column4...

You can put... oh, you did already :)

To be honest it's the first quesiton I've asked in ages since you didn't have to ask Comm Support to split points fo you so kinda did it wrong.  I already put in a CS Q earlier asking for re-open and correct :-)

BTW if you derive extra columns based on the others using "display multi value as seperate" then it's the same old.  So it seems if you have columns with 'n' multi-values in then in line 1 you will see value , line 2, value 2 as long as each column as "display multi values as seperate" ticked otherwise you see al values.  Haven't checked what happens when you have it categorised by something with 4 values say and only 2 items in other fields, at a guess it will go 1 2 1 2...

Notes still surprises me after all these years and once I 'got' how multi-value fields worked back in R3.0 I don't know how other db systems do without such a brilliant concept!

Will sort out the points and selected answer once CS do their stuff.

thanks

Steve

Hope that's a bit fairer.  As I said before 500 points doesn't do justice to the help you get around here when you need it!

thanks

Steve

Let's hope you need more... ;)

Thanks!