HCLSoftware Digital Solutions Community

An you find my @DBLookup Key?

HCL Domino Domino Forum

hcl-bot October 26, 2007, 5:04am 1

Hi Guys,I’m looking for some more help with reference to a DBLookup that I’m trying to do, as I can’t figure out what the key should be.

Basically I’ve created a view that allows me to view data that is categorised by Fiscal year, Quarter then by month and by business year.

My first column uses the following code to get the fiscal year: -

@If(@Month(DateField) <=3;"Fiscal Year " + @Text(@Year(DateField)-1);"Fiscal Year " + @Text(@Year(dateField)))

My second column is coded :-

M1 := @Month(DateField);

@If(M1 = 1 : 2 : 3; “Qtr 4”; M1 = 4 : 5 : 6; “Qtr 1”; M1 = 7 : 8 : 9; “Qtr 2”; “Qtr 3”)

My Third column used :-

@Month(DateField)

My fourth column is :-

m := @Right(“0”+@Text(@Month(DateField));2);

@If(@Contains(m;“01”); “January”;@Contains(m;“02”);“February”;@Contains(m;“03”);“March”;@Contains(m;“04”);“April”;@Contains(m;“05”);“May”;@Contains(m;“06”);“June”;@Contains(m;“07”);“July”;@Contains(m;“08”);“August”;@Contains(m;“09”);“September”;@Contains(m;“10”);“October”;@Contains(m;“11”);“November”;@Contains(m;“12”);“December”;“”)

The next column returns the value of a dialogue list box that returns one of 5 Business Units (BU).

The following columns just return lots of data from number fields on the form.

I want to be able to pull off all the data for a particular Business Unit for a quarter. I understand that the best way to do this is to create a DBLookup that looks for the correct key that needs to be in the first column of my lookup view.

I’m struggling to work out how this “key” should be coded as I’m guessing that I need to concatenate some columns?

Any advise on the best way forward would be really appreciated.

Thanks in advance for your time.

Regards

Charlie

hcl-bot October 26, 2007, 6:05am 2

Subject: an you find my @DBLookup Key ?

Hi Charlie,

For this “problem”, I create a working view (view hidden for users) let’s say “workViewForLookup” and in first column I concatenate keys e.g. :

(key1+key2) : (key1+key3) : (key3+key1) : and so on

so, my @dblookup can be :

@dblookup(server:database;“workViewForLookup”;key1+key2;column) or

@dblookup(server:database;“workViewForLookup”;key3+key1; column) and so on

but, If you have many documents, create several views with one key…

In fact I have view for display (use by users) and view the working (@ formulas, Lotuscript)…

Also, if you do change the display view, you don’t need to modify your DBLookup…

HTH

Thierry

hcl-bot October 26, 2007, 7:44am 3

Subject: Can you find my @DBLookup Key ?

More thanks Thierry.

I have used Key1 + Key 2 + Key 3 in a view with the first column categorised. The code is -

@If(@Month(Month) <=3;"Fiscal Year " + @Text(@Year(Month)-1);"Fiscal Year " + @Text(@Year(Month))) + M1 := @Month(Month);

@If(M1 = 1 : 2 : 3; “Qtr 4”; M1 = 4 : 5 : 6; “Qtr 1”; M1 = 7 : 8 : 9; “Qtr 2”; “Qtr 3”) + " " + BU

This seems to work a treat, even though it looks rather frightening to me!

Thanks for all your help - much appreciated.

hcl-bot October 26, 2007, 7:58am 4

Subject: RE: Can you find my @DBLookup Key ?

Opps I spoke too soon. The look up view works fine and returns the correct documents. However it’s with the actual DBLookup in the field in a form that I’m struggling with. I can’t figure out the exact code that I need to use as the key. I’ve tried to replicate the same code as in my View Look up but that fails (incorrect data type).

I’ve tried to use the code below as my key but it fails (incorrect datatype):-

@Sum(@DbLookup(“”;“”:“”;“LUQ”;@If(@Month(Month) <=3;"Fiscal Year " + @Text(@Year(Month)-1);"Fiscal Year " + @Text(@Year(Month))) + M1 := @Month(Month);

@If(M1 = 1 : 2 : 3; “Qtr 4”; M1 = 4 : 5 : 6; “Qtr 1”; M1 = 7 : 8 : 9; “Qtr 2”; “Qtr 3”) + " " + BU ;2))

I’m just trying to total with quarterly totals for each business unit and pull these into a new form.

Any ideas? Your help is much appreciated.

Regards

Charlie

hcl-bot October 26, 2007, 8:03am 5

Subject: RE: Can you find my @DBLookup Key ?

I think your problem is in this line:

@Sum(@DbLookup(“”;“”:“”;“LUQ”;@If(@Month(Month) <=3;"Fiscal Year " + @Text(@Year(Month)-1);"Fiscal Year " + @Text(@Year(Month))) + M1 := @Month(Month);

at the very end you use the assignment operator “:=” on the right side of a formula. Is this intentional? I would think you can only put variables or fields on the left side of the assignment operator.

hcl-bot October 26, 2007, 8:27am 6

Subject: RE: Can you find my @DBLookup Key ?

Hi Cesar,that part of my code determines which month should fall into which quarter in my lookup view. If I lose that part then the correct selection is not made. My problem is that I can’t work out how to describe/code the actual key in my lookup field on the form.

I am aware that my DBLookup code in my form field has to use the same key as in my lookup view and that’s where my problem seems to be.

Regards

Charlie

hcl-bot October 26, 2007, 8:43am 7

Subject: RE: Can you find my @DBLookup Key ?

My suggestion is not to work in one long line of code. I am certain that your intention was not to use an assignment operator within the @DbLookup. Here is what you had (after breaking up the long line into manageable pieces):

=======================================

@Sum(

@DbLookup(

	"";

	"":"";

	"LUQ";

	@If(

		@Month(Month) <=3;

		"Fiscal Year " + @Text(@Year(Month)-1);

		"Fiscal Year " + @Text(@Year(Month))

	) + M1 := @Month(Month);

	@If(

		M1 = 1 : 2 : 3;

		"Qtr 4";

		M1 = 4 : 5 : 6;

		"Qtr 1";

		M1 = 7 : 8 : 9;

		"Qtr 2";

		"Qtr 3"

	) + " " + BU ;

	2

)

)

=======================================

and here is what you probably intended to have (again, broken up into pieces for better clarity):

=======================================

M1 := @Month(Month);

@Sum(

@DbLookup(

	"";

	"":"";

	"LUQ";

	@If(

		@Month(Month) <=3;

		"Fiscal Year " + @Text(@Year(Month)-1);

		"Fiscal Year " + @Text(@Year(Month))

	) + 

	@If(

		M1 = 1 : 2 : 3;

		"Qtr 4";

		M1 = 4 : 5 : 6;

		"Qtr 1";

		M1 = 7 : 8 : 9;

		"Qtr 2";

		"Qtr 3"

	) + " " + BU;

	2

)

)

=======================================

hcl-bot October 26, 2007, 10:14am 8

Subject: RE: Can you find my @DBLookup Key ?

OK Guys, I think you are right in that my code is too long and complicated. I have now cut down the code in the first column of my LookUp View to -

M1 := @Month(Month);

@If(

M1 = 1 : 2 : 3;

“Qtr 4”;

M1 = 4 : 5 : 6;

“Qtr 1”;

M1 = 7 : 8 : 9;

“Qtr 2”;

“Qtr 3”

) + " " + BU

Can you suggest what the key/code should be in my field DBLookup code?

Many thanks for your patience and continued support.

Regards

Charlie

hcl-bot October 26, 2007, 10:15am 9

Subject: RE: Can you find my @DBLookup Key ?

OK Guys, I think you are right in that my code is too long and complicated. I have now cut down the code in the first column of my LookUp View to -

M1 := @Month(Month);

@If(

M1 = 1 : 2 : 3;

“Qtr 4”;

M1 = 4 : 5 : 6;

“Qtr 1”;

M1 = 7 : 8 : 9;

“Qtr 2”;

“Qtr 3”

) + " " + BU

Can you suggest what the key/code should be in my field DBLookup code?

Many thanks for your patience and continued support.

Regards

Charlie

hcl-bot October 26, 2007, 8:56am 10

Subject: RE: Can you find my @DBLookup Key ?

Well, a code a little more orderly would be very helpful, but that’s the end of hardest week of work, isn’t it ?

Something like this, should work better:

Rem {Return month};

M1 := @Month(Month);

Rem {from the month, return quarter};

whatQtr := @If(M1 = 1 : 2 : 3; “Qtr 4”; M1 = 4 : 5 : 6; “Qtr 1”; M1 = 7 : 8 : 9; “Qtr 2”; “Qtr 3”);

Rem {Compute the key for lookup};

myBeautifulKey := @If(@Month(Month) <=3;"Fiscal Year " + @Text(@Year(Month)-1);"Fiscal Year " + @Text(@Year(Month)+whatQtr)) ;

Rem {Compute total};

totalPerUnit := @Sum(@DbLookup(“”;“”:“”;“LUQ”;myBeautifulKey));

totalPerUnit

Sorry, if this is not THE solution for your problem but I guess it should put you on right way…

Regards (from France)

Thierry

hcl-bot October 26, 2007, 9:17am 11

Subject: RE: Can you find my @DBLookup Key ?

Thanks for you help Guys. Thank god it’s Friday is all I can say. I’ll give your suggestions a go and see what happens. Your help is appreciated.Regards

Charlie

hcl-bot October 29, 2007, 4:58am 12

Subject: Monday morning blues

Hi Guys, firstly thanks for all your help last week. I have decided to make the DBLookup as simple as possible. I now only require the DBLookup key to be :-

1st column of view.

M1 := @Month(Month);

@If(M1 = 1 : 2 : 3; “Qtr 4”; M1 = 4 : 5 : 6; “Qtr 1”; M1 = 7 : 8 : 9; “Qtr 2”; “Qtr 3”)

So my DBLookup code is :-

@Sum(@DbLookup(“”;“”:“”;“LUQ”;M1 := @Month(Month);

@If(M1 = 1 : 2 : 3; “Qtr 4”; M1 = 4 : 5 : 6; “Qtr 1”; M1 = 7 : 8 : 9; “Qtr 2”; “Qtr 3”) ;3))

However this returns an incorrect datatype for database functions. Any ideas would be appreciated?

hcl-bot October 29, 2007, 7:06am 13

Subject: Waving a white flag!

Ok ok so I’m a coward and I’ve given up trying to find the key. I’ve decided to put a couple of hidden computed for display fields on the form that return the fiscal year and the Quarter. In my lookup view I have concatenated the field names together with the BU field (FYear + QYear + BU). This then forms my Key in my DBLookup code.

Probably not the best or the most dynamic way to do it but it seems to work ok.

Thanks to everyone for their help.

hcl-bot October 29, 2007, 7:51am 14

Subject: You shouldn’t have given up

Your problem was not with the logic but a syntax error (one I had warned you about previously here: http://www-10.lotus.com/ldd/nd6forum.nsf/DateAllFlatweb/9902152bee5557d485257380004b6cd1?OpenDocument ). In you last posting here:

http://www-10.lotus.com/ldd/nd6forum.nsf/DateAllFlatweb/6a85efe754c28d0c852573830036c4bb?OpenDocument

you said that your lookup code was:

@Sum(@DbLookup(“”;“”:“”;“LUQ”;M1 := @Month(Month);

@If(M1 = 1 : 2 : 3; “Qtr 4”; M1 = 4 : 5 : 6; “Qtr 1”; M1 = 7 : 8 : 9; “Qtr 2”; “Qtr 3”) ;3))

However, you are using the assignment statement

M1 := @Month(Month);

in the middle of the @DbLookup. That is likely the only problem you had.

hcl-bot October 29, 2007, 10:30am 15

Subject: RE: You shouldn’t have given up

Hi Cesar,I did try the method that you suggested but unfortunately that didn’t work for me either as it kept returning error messages. I decided to use some hidden fields instead as I needed to finish off some views quickly for my users.

Please don’t think that I ignored your advice or didn’t appreciate it, as I found it extremely helpful. When I’m under less pressure I will revisit this problem and try to solve it. Once again many many thanks for your help.

hcl-bot October 29, 2007, 10:46am 16

Subject: RE: You shouldn’t have given up

OK, no problem - I thought you might have missed the posting regarding the assignment operation.