Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Multiple values in view issue

Posted on 2006-04-20
28
Medium Priority
?
424 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:Steve Knight
  • 12
  • 10
  • 3
  • +1
27 Comments
 
LVL 43

Author Comment

by:Steve Knight
ID: 16496729
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
 
LVL 22

Expert Comment

by:mbonaci
ID: 16496751
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
 
LVL 43

Author Comment

by:Steve Knight
ID: 16496813
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
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.

 
LVL 43

Author Comment

by:Steve Knight
ID: 16496889
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
 
LVL 22

Expert Comment

by:mbonaci
ID: 16497035
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
 
LVL 43

Author Comment

by:Steve Knight
ID: 16497124
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16497503
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16497521
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
 
LVL 18

Assisted Solution

by:marilyng
marilyng earned 600 total points
ID: 16499898
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16500001
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
 
LVL 43

Author Comment

by:Steve Knight
ID: 16501951
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
 
LVL 43

Author Comment

by:Steve Knight
ID: 16502511
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16502672
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16502828
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
 
LVL 18

Expert Comment

by:marilyng
ID: 16502835
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
 
LVL 43

Author Comment

by:Steve Knight
ID: 16502863
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
 
LVL 43

Author Comment

by:Steve Knight
ID: 16502880
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16502926
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
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 1400 total points
ID: 16502936
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
 
LVL 43

Author Comment

by:Steve Knight
ID: 16503016
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16503049
0:17 over here. I think I'm going to have a look at the ceiling of my bedroom...
0
 
LVL 43

Author Comment

by:Steve Knight
ID: 16503130
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
 
LVL 18

Expert Comment

by:marilyng
ID: 16503802
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16505286
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
 
LVL 43

Author Comment

by:Steve Knight
ID: 16505335
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
 
LVL 43

Author Comment

by:Steve Knight
ID: 16507414
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16507501
Let's hope you need more... ;)

Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question