Solved

@Dblookup

Posted on 2006-06-26
28
775 Views
Last Modified: 2013-12-18
I have a product database that looks up price and description from a product lookup table(@DBlookup), the problem I have is that sometimes we change product names,prices and so on, if someone refreshes an old invoice it will change the information on that invoice, is there anyway of making the old invoices just store the info that was recorded at the time they were saved.
0
Comment
Question by:NSCexpert
  • 8
  • 7
  • 5
  • +3
28 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16983029
I assume the information is stored correctly in the documents, since the result of the @DbLookup is stored (verify this using the Document Properties from a view). Only when reopening the document using a form, the field will be recomputed, and that's something you dont want. The old invopices shouldn't be changed, but the form they are displayed with. I hope there is sime status on the form as well, so that any document with a status <> "Draft" isn't modified.

If the formula in the productname field is something like
    @DbLookup("";"";"View";PCode; 1)
change it to
    @If(status!="Draft"; @ThisValue; @DbLookup("";"";"View";PCode; 1))

Or something similar.
0
 
LVL 13

Expert Comment

by:CRAK
ID: 16983720
If you only retrieve (and store?) the parent's UNID, you may gain some speed by using @GetDocField instead of @DbLookup:

@GetDocField( documentUNID ; fieldName )
0
 
LVL 43

Accepted Solution

by:
Steve Knight earned 250 total points
ID: 16983864
True, it depends upon the points in time that the values need lookign up, another way is to use @IsNewDoc for instance but the status idea is more reliable IMHO.

Myself I think I'd just do put the lookups in the fields at all, i.e. make the fields computed with the fieldname as value or even editable if approriate then use a lookup button or action to carry out the @dblookups and populate the other fields.  Add to that with some logic to say if a lookup is needed (i.e. product code or qty has changed etc.) before the document can be saved.  You can then use the same code to update a audit history of changes etc. if needed.

I can't think of any logical reason why you'd need to go and update a set of documents after the event so leaving @dblookups in computed fields is quite likely to cause problems really -- I certainly wouldn't want to rely on any financial reports that came out of the system.

Steve
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16985495
Call me stupid, but have you considered changing the field from COMPUTED, to COMPUTED WHEN COMPOSED?

If you need to push a value into the field from a button, then make the default formula: ""  (Null, double quotes)
This way, a button or script can still update the formula but only when you want it updated.

:)))
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16985860
Just a silly question: how do you propose to edit those CwC-fields in a new document?
0
 
LVL 14

Expert Comment

by:p_partha
ID: 16987147
I believe marilyng is right, JUst make the field computed when composed,

sjef bro, he is anyway looking up ... not entering the info

Partha
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 16987229
Hmm, so how does a Computed When composed field with value "" differ from using a computed field with itself as value if the data is being updated by a button (both previosuly suggested).  Once the field exists even if it is "" then it cannot calculate again unless it is gone with a @DeleteField.

I suppose it does mean potentially slightly less calculating for Notes to do as a CwC field won;t get recalculated but that is it, surely?

Steve



0
 
LVL 13

Expert Comment

by:CRAK
ID: 16987331
I think there's a misunderstanding:
Marilyn wrote:
   If you need to push a value into the field from a button, then make the default formula: ""
See? "IF"! A button can override the value stored in a CwC field!
But in the creation of a document, you could (probably) retrieve your product data the same way you do now (or improve it by cutting back on the no. of @DbLookups). Computed when composed will keep old invoices from retrieving the latest data.

0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 16987882
I imagined the user chooses a product from an editable field then it looks up the values and details so I didn't see the point in a CwC field in this case as the @dblookup would have to be in a button anyway if not directly as the formula for computed field.

Anyway it's all the same answer really...

Steve
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16987995
Hi NSCexpert, some feedback please...
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16988803
The computed-when-composed fields are just that, computed when composed.  If, however, you want to push stuff to the field, either in a validation event, or in script or update, then you make the formula = "", then you can change the field value.

This was an R3 workaround that still works.   Typically, you add another field on the form with a validation event that does the lookups - I never cared for that method, because then you had this series of empty fields at the bottom of your form that all contained "push" validation code and was difficult to maintain.   Each validation event set some value, and the last field would check those previous values before it would update the "computed when composed" field.

:)
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16988933
dragon-it,

Here is the scenario:

Create a form with three fields:  field1 = computed, @UserName;
Field2: Computedwhencomposed, formula="";
Field3: Editable;default formula=""; Validation formula:
         @If(@IsDocBeingEdited & @IsDocBeingSaved;
         @SetField("Field2";"Here is the Validation");""); @Success

Add a button: with the formula:
@SetField("Field2";"Here is a button value")

Open the form for editing - field 2 is blank.
Push the button, field 2 becomes "Here is the button value"

Press Ctrl+s, close and reopen the form.
Field 2 becomes: "Here is the Validation"


So, field3 ultimately controls what appears in Field2.  It will not recalculate unless you make it recalculate.

------
Yes, same is true for a computed field2 with a formula of field2, it will only change when you tell it to change.
-----
In this case, the user only wants to lookup the value once, so if you put @dblookup in the formula for computed when composed, it will ONLY change the value the first time the form is saved, and not continue to do the lookup on subsequent saves.  You can still push a new value into the field via button or validation event, i.e. if you want to refresh the lookup to reflect the latest values.

Make sense?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16990295
Double fields... hm. I prefer single fields in combination with a status field. Question of taste?
0
 

Author Comment

by:NSCexpert
ID: 16990844
Thanks everybody for your input, its unleashed many possibilities in my mind, think I need to redesign and go with a calculate button to populate the fields instead of a direct dblookup. Take Steves point on financial data(Very Valid). But........ I will still need my original dblookup to choose the product. What if I rename a product what would be the best way of dealing with this ?(or will the computed when composed still save the old name if I revise the doc)

K
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 43

Expert Comment

by:Steve Knight
ID: 16990945
It would still have the old name and all the related values would be the same.  The thing is if it is an invoice nothing should change really should it?

You could an an "old names" field to the product then add this as a multi-value in your lookup view for procucts, i.e. then if you change the name it is still available and findable under the old name too.  Another way is to assin a product code which doesn't change even if the description does I guess.

Col1: prodname:oldname    "display multiple values as seperate entries"
Col2: price
etc.

Steve
0
 

Author Comment

by:NSCexpert
ID: 16991266
Thanks I understand this clearly.
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16994523
sjef.. I was trying to illustrate for Steve, how the computed when composed fields work.  Sheesh!  Computed when composed is exactly the definition of NSCExpert's question: how do I not make the field update and collect the new values.

But, I see that NSCExpert is going with Steve's suggestion.

Best of luck!
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16994562
Steve, as CRAK indicated, you have misconceptions about the CWC field, you can change the value, but just when you want to.  And by virtue of the CWC field, it will only change when you tell it to change.  With this you don't need to "change" anything in your documents or forms except the field type, and (test on a separate database) run a tools refresh all documents to set theCWC field.  CRAK, is that right?

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16994708
Hi Marilyn, it was more or less a rethorical question. I'm just not so fond of Corrupted when Composed fields, because they cannot be changed (or couldn't?) using Formula language.

Anyway, I'd love to know what triggered NSCexpert to take Steve's for an answer, and what his/her solution will be.
0
 
LVL 13

Expert Comment

by:CRAK
ID: 16995147
Huh? Do I have misconceptions now, Marilyn?... "tools refresh all documents to set theCWC field."????
I don't think so! But { Field CwC:=newValue } or { @SetField(...) } may help under certain conditions (not if the doc is still open in edit mode), but I must admit: I haven't tried over the last couple of releases. Some of my "best practices" from the R4.5 era still stand strong!
That, I must admit, is an advantage of (contidionally) hidden editable fields: hidden or not, you can force new values into them!
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16996854
>>tools refresh all documents to set theCWC field<<

I'm not sure that that you would need to do this, but would test to be sure.  Else, the first time someone opened an old document and put it into edit mode, the question would be would it lookup the value first, or realize that it is a CWC field?

I remember having to refresh the documents for some of the design changes, but can't recall which were imperative. :)
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16996875
sjef - >>because they cannot be changed (or couldn't?) using Formula language<<

Not true, they can be changed.  Maybe not by script?

I'm guessing NSCExpert chose the solution because both you and steve made CWC fields sound like a plague to avoid.  

Remind me to return the favor sometime ;)
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 16996946
Been away a bit, missed a lot of comments :-)

Maybe I missed the point and this is assumption but I assumed something like this on his forms as it is an invoice apparently

Product ID #1  Editable or dialog lookup
Product Price #1 (calculated looked up currently using @dblookup)
Product Descripton #1 (calculated looked up curently using @dblookup)
etc.

Changing these to CwC would stop the functionality of the form without additional code to push the values into the CwC fields using a button or elsewhere as has been suggested.  I believe all I suggested was making them calculated with value of the fieldname and using a "lookup" button therefore no chance of them being mistakenly updated, old documents changed or the functionality failing in new documents.

A CwC field would do exactly the same job I agree, starting with a null value and being populated by button, validation elsewhere etc. nut I just didn;t see the point in using them in this instance.  I am aware of how they can be used and often use them in their traditional sense to pickup information on initial document creation etc.  I do know they can be changed later, can;t say I've had any problem with amending their value myself.

Anyway, I need the points more than you guys, 50k in this TA is OK... for starters :-)

Steve
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16997488
Aaaargh... !!  Making them calculated with a fieldname ALSO stops the functionality.  DUH.  

CWC: you add a button if you want to refresh the values with the latest and greatest lookup, but don't need to change the field formula.

Computed with formula = itself.  Well,  you HAVE to add a button, AND you HAVE to add PostOPEN formula to lookup the first value automatically.  Because when the form first opens the formula=itself will do NO lookup.

Your solution, while it is probably the way I would design the application if I were doing it from scratch, might not be the elegant approach to the question on hand: a currently active, production, database.  

Experts-exchange is not about points.


0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 16998622
Marilyn,

Read my last commetn and his Q.  IMHO there is no default value for this field at all and presumably these are existing fields so CwC will not assist at all.

I agree CwC and computed with formula as fieldname which is what I suggested all along could do the same in this instance.  I never disagreed with that.

Looking back you say about CwC 'if you put @dblookup in the formula for computed when composed, it will ONLY change the value the first time the form is saved' - Maybe I am wrong here but I believed the formula to be calculated when initially composed and then never again unless removed from the document.  Of course you can push lookup into it.  If you add @dblookup into field and also a button then the duplication surely could lead to errors too if both formula are not maintained at the same time.  In any case in a new doc. there is nothing to lookup yet and in an old doc. the information is already there.

IMHO EE is everything about points.  I like answering q's and helping out and furthering my own knowledge but at the end of the day earning points awarded by people wanting that answer and seeing your name rise up the HOF is the only actual 'reward' for the work.

Steve
0
 
LVL 13

Expert Comment

by:CRAK
ID: 16999202
"...need the points more than..."?
"...rise up the HOF..."?

dragon-it, are you trying to overtake anyone in particular in e.g. this year's HOF?
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 16999386
No, just in general, in many ta's. Was 750 overall before now <500.  Can't compete with you guys that have been here long time and not going to try!




0
 

Author Comment

by:NSCexpert
ID: 16999498
Now Now Experts!, If I could distinguish between every solution that was offered I probably wouldnt need to be posting questions on this site, Many thanks for all your input but I simply picked the solution which I thought would be easiet to implement based on my understanding on Notes.

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now