@Dblookup

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.
NSCexpertAsked:
Who is Participating?
 
Steve KnightConnect With a Mentor IT ConsultancyCommented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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
 
CRAKCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
marilyngCommented:
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
 
Sjef BosmanGroupware ConsultantCommented:
Just a silly question: how do you propose to edit those CwC-fields in a new document?
0
 
p_parthaCommented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
CRAKCommented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
Sjef BosmanGroupware ConsultantCommented:
Hi NSCexpert, some feedback please...
0
 
marilyngCommented:
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
 
marilyngCommented:
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
 
Sjef BosmanGroupware ConsultantCommented:
Double fields... hm. I prefer single fields in combination with a status field. Question of taste?
0
 
NSCexpertAuthor Commented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
NSCexpertAuthor Commented:
Thanks I understand this clearly.
0
 
marilyngCommented:
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
 
marilyngCommented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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
 
CRAKCommented:
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
 
marilyngCommented:
>>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
 
marilyngCommented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
marilyngCommented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
CRAKCommented:
"...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
 
Steve KnightIT ConsultancyCommented:
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
 
NSCexpertAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.