Multiple values in view issue

Being scratching my head on this one.

I've got two numeric fields FieldA and FieldB containing currency amounts and two date fields FieldAPaidDate and FieldBPaidDate

FieldA an FieldB are related but independent amounts which may be awarded and therefore paid out out on different dates.

Creating one view for what still requires paying and totalling it is easy enough by only diplaying FieldA/B in the column if their corresponding PaidDate has not yet been completed but what I need is a view as :

date (actually split into yyyy month etc. but that is OK).  FieldAPaidDate:FieldBPaidDate multi-value categorised
       Field A    Field B   Other fields

I would like Field A only to appear and therefore be totalled when the date in the categorised first column is the correct one.

I would prefer not to have two views, one each for FieldA and FieldB but need the totals to add up correctly as otherwise when both have been paid they will both show up two dates.

This is running on R5 at the moment.

thanks

Steve
LVL 44
Steve KnightIT ConsultancyAsked:
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.

Steve KnightIT ConsultancyAuthor Commented:
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
0
mbonaciCommented:
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
0
Steve KnightIT ConsultancyAuthor Commented:
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
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Steve KnightIT ConsultancyAuthor Commented:
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

0
mbonaciCommented:
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
0
Steve KnightIT ConsultancyAuthor Commented:
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
 
0
Sjef BosmanGroupware ConsultantCommented:
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...
0
Sjef BosmanGroupware ConsultantCommented:
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...
0
marilyngCommented:
Isn't this something like: http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_21784267.html

funny, sjef, you suggested the same approach before, too...

Dragon-it, it's a long explanation with several examples, but worth the examples presented on how to solve your view presentation problem. (Maybe.. I just skimmed this above, so I may be wrong)
0
Sjef BosmanGroupware ConsultantCommented:
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...
0
Steve KnightIT ConsultancyAuthor Commented:
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
0
Steve KnightIT ConsultancyAuthor Commented:
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

0
Sjef BosmanGroupware ConsultantCommented:
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..
0
Sjef BosmanGroupware ConsultantCommented:
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.
0
marilyngCommented:
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.
0
Steve KnightIT ConsultancyAuthor Commented:
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
0
Steve KnightIT ConsultancyAuthor Commented:
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
0
Sjef BosmanGroupware ConsultantCommented:
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?
0
Sjef BosmanGroupware ConsultantCommented:
Totally, utterly amazed...

Try
    x:= @Word(@Left($Column1;"ECM");"|";2);
    @If(@IsError(x); ""; x)
So don't Trim, and forget the @TextToNumber, totalling will work anyway :)
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
Steve KnightIT ConsultancyAuthor Commented:
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
Sjef BosmanGroupware ConsultantCommented:
0:17 over here. I think I'm going to have a look at the ceiling of my bedroom...
0
Steve KnightIT ConsultancyAuthor Commented:
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
0
marilyngCommented:
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.
0
Sjef BosmanGroupware ConsultantCommented:
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 :)
0
Steve KnightIT ConsultancyAuthor Commented:
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
0
Steve KnightIT ConsultancyAuthor Commented:
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
0
Sjef BosmanGroupware ConsultantCommented:
Let's hope you need more... ;)

Thanks!
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.