Why formula did not get refresh upon data upload?

[hcl-bot]

I created a lookup view, called AccountView.

In my form budget, I created a field called AccountCode, which both “refresh field” and “refresh choices” are ticked.

Below this field, is AccountDesc Field with the following formula,

@If(@IsError(@DbLookup( “” : “NoCache” ; “” : “” ; “AccountView” ; AccountCode ;4 ));“”;@DbLookup( “” : “NoCache” ; “” : “” ; “AccountView” ; AccountCode ; 4 )).

The issue is that the refresh only works after data being uploaded, and I click the agent to refresh this document. (Using 'ToolsRefreshSelectedDocuments)

I want the refresh to be done upon upload.

Some fields are refreshed upon upload, some are not. That is what puzzles me. and the lookup formula is same as above.

Please help.

[hcl-bot]

Subject: Why formula did not get refresh upon data upload?

Hi Will,

Lets try going back to some basics to see if we can work this out:

I created a lookup view, called AccountView.

In my form budget, I created a field called AccountCode, which both “refresh field” and “refresh choices” are ticked.

Below this field, is AccountDesc Field with the following formula,

DEBUG STEP ONE… Manually type the value you expect to see in the field AccountCode into the field as a default vlaue or while the document is in edit mode.

Hit F9 while in the document to see if AccountDesc field refreshes as expected.

DEBUG STEP TWO

Change the AccountDesc field to be Text \ Computed For Display and make the following the formula.

lu := @DbLookup( “” : “NoCache” ; “” : “” ; “AccountView” ; AccountCode ;4 );

@If(@IsError(lu );“An Error Occurred”;lu )

Then place the lookup value into AccountCode and hit F9 to refresh the document. does this return a different result?

Try saving the document and reopening it. Does this bring up the correct values?

DEBUG STEP THREE

If either of the above worked then make sure your AccountDesc field is Text, computed. Check refesh fields on doc refresh option. Use the same formula as in Step two above. Try using F9. If that fails try doing a Control S (Save and see if the values change).

If none of the above work check your values are correct in the lookup view.

The issue is that the refresh only works after data being >uploaded, and I click the agent to refresh this document. >(Using 'ToolsRefreshSelectedDocuments)

What do you mean by uploaded? If you are loading Accountcode into the back ground doc using an agent then you will need to do a Doc Compute With Form or similar in the agent populating the docs to ensure all the values formulas are run. You may need to refresh the docs twice if there are lookups relying on fields which themselves are lookups.

DEBUG TIP … when using a DB lookup and debugging, don’t use the formula

@DbLookup( “” : “NoCache” ; “” : “” ; “AccountView” ; AccountCode ;4 );

@DbLookup( "A WHOOPING GREAT ERROR OCCURRED " + lu : “NoCache” ; “” : “” ; “AccountView” ; AccountCode ;4 );

So you can see the difference between errors and failures to find data in the view.

Also try ditiching all the formulas with two lookups such as

@If(@IsError(@DbLookup( “” : “NoCache” ; “” : “” ; “AccountView” ; AccountCode ;4 ));“”;@DbLookup( “” : “NoCache” ; “” : “” ; “AccountView” ; AccountCode ; 4 ))

and go to the lu := style I suggested. It may solve your problem wif it is a double refresh error.

HTH,

Shane

[hcl-bot]

Subject: Why formula did not get refresh upon data upload?

Have you got the option ‘Automatically refresh fields’ ticked in the first tab of the Form properties?

[hcl-bot]

Subject: Why formula did not get refresh upon data upload?

Hi Will,

My first suggestion would be to remove the double look up you currently have in your code. It may be you are doing th elookup twice and so it is failing. Here is what I mean. At the very least it will reduce the number of lookups and speed up your application.

Currently:

@If(

     @IsError(@DbLookup( "" : "NoCache" ; "" : "" ; "AccountView" ; AccountCode ;4 ));

       "";

     @DbLookup( "" : "NoCache" ; "" : "" ; "AccountView" ; AccountCode ; 4 )

   ).

You are saying, if the lookup is an error then put nothing otherwise do the lookup again. Instead do the lookup once, which may solve your refresh problem, like this:

lu := @DbLookup( “” : “NoCache” ; “” : “” ; “AccountView” ; AccountCode ; 4 );

@if(

   @IsError( lu ) ; 

          ""; 

    lu

 )

It may resolve the refresh problem.

HTH,

Shane

[hcl-bot]

Subject: RE: Why formula did not get refresh upon data upload?

Thank you, Shane, but bit curious, doesn’t by putting variable LU, it still tells notes to run the lookup first? i.e still ended with 2 lookups?

I don’t think this is the problem, as the other field, with similar formula, works, it got updated upon lookup, just this field, it does not.

Thank you.

[hcl-bot]

Subject: RE: Why formula did not get refresh upon data upload?

Hi Will,

Using the LU variable means the lookup is only done once. I’ll talk you through it …

REM { Does a look up here and stores the results in the variable lu};

lu := @DbLookup( “” : “NoCache” ; “” : “” ; “AccountView” ; AccountCode ; 4 );

REM { Doing a check here to see if the lu results stored in lu returned an error};

@if(

@IsError( lu ) ;

REM{ If it is an error return nothing at all};

“”;

REM {Else return the vlaue of lu here};

lu

)

It may be you are thinking of this as you would a scripting language (especially in an *Nix environment. There you assigne scripts to variable and run the code by using the variable and if needs be escaping the variable using single or double quotes to get the exact returns you want. Here the line

lu := …

has the values returned from the dbLookup explicitly returned to it. This means the lu used in the @if statement is only ever the result fo the lookup. Your initial code was forcing the lookup to happen twice.

This means two things:

1 - Double the work on the network as double the lookups if the lookup is successful

2 - Dependant lookups in computed or computed for display fields may not return the values you expect as a double refresh of the fields may be required to return a result into the first field which supplies keywords for the second lookup.

Can you post the formula from your fields or email me a small nsf file with your form, the view it uses and possibly some sample data in it and I’ll have a look at it for you .

email is shane_hollis2001 at yahoo dot com.

I also listen to that for IRC messages as well. If you are on line at the same time as I am feel free to pop an IRC to me and see if we can hook up in real time to discuss this.

HTH,

Shane