DB Column / dialoge list / new values don't get added

[Fret4u]

I have a form to keep track of invoices. One field is for VAT. Since I get invoices from all over the world, I have to store different VAT %. I use a diologbox and a formula to choose values, This is the formula: @DbColumn("":"NoCache";"";"Maintenance/VAT";1). This column reflect to a (sub)view called VAT. The first (and only) column displays VAT % like 0, 6, 17.50, 19. This column also displays words "dev." to say that an invoice has different VAT %.With the above formula I can retrive the data for the field "VAT" from the view "Maintenance/VAT". That works alright. The problem is that new value's are not stored, e.g. if I get an invoice from Belgium the VAT % is 21. I can edit the field VAT (which default value is 19) and change the 19 into 21. Then I save the record but when I add a new invoice the keywords don't include the new added keyword. I now work around that problem by adding the value directly into the view "Maintenance/VAT". Then I go back to the form and choose the value from the keyword list. Standard templates however store new keywords, but I can't get it to work. What am I doing wrong, because in my opinion I use the correct formula. Any suggestions anybody ??

 

[pmonett]

It's me again.
Dynamic keyword lists is an old trick in Notes. All you need is to list all docs with a VAT value in a special view (can be hidden). This view needs two columns, the first is the VAT percentage, the second is anything you want.
Sort the first column and categorize it. Then use that view in your @DbColumn lookup you put as keyword formula.

Simple and works like a charm.

Pascal.

 

[Fret4u]

I already have created this view and named it "Maintenance/VAT". But it has one column called VAT. And it is sorted. I also used that name of the view in my @DBcolumm lookup. In my opinion I already have done all that you suggested, or did I forget something ???

 

[pmonett]

Did you tell your keyword formula do not use the cache ?
It's the NoCache option in the @DbColumn syntax. If you do not use NoCache, then your keyword list will be cached for the length of your session, which means you'll have to at least close the db before the list is refreshed.

Pascal.

 

[Fret4u]

Pascal, the formula that I use now is: @DbColumn("":"NoCache";"";"Maintenance/VAT";1. In that formule I use NoCache....should I use Cache instead (so the formula would be @DbColumn("":"Cache";"";"Maintenance/VAT";1) ) or should I leave the "Cache string" blank (so the formula would be @DbColumn("":"";"";"Maintenance/VAT";1) ) ?

 

[pmonett]

Hmm, I would leave the formula as it is. Theoretically, it should re-perform the lookup at every new attempt, and catch the keyword updates.
The first column of Maintenance/VAT is the VAT rate ? And every new document shows up in that view immediately ?

Pascal.

 

[Fret4u]

The first row in the particular view is indeed the VAT rate. When I add a new invoice (through the form invoice)and the VAT rate is not in the keyword list I add the new rate through the "workaround". That is to say: I have an action button @Command(Compose(VAT)). The new VAR rate shows up immediately in the view of the VAT rates but not in my keyword list. Only after I saved (and closed) the new document (in this case; invoice) and re-open it, the new VAT rate shows up in the keyword list. Does that help you further ? Rather a tricky problem, isn't it ?

 

[pmonett]

I think I have the answer - you need to activate the Refresh Choices on Document Refresh flag in the field properties.

Tell me it is not checked !

Pascal.

 

[Fret4u]

Pascal,
I'll try it. Will let you know if this solves the problem.
Fret

 

[Fret4u]

Sorry Pascal,
I have tryed it but it didn't solve the problem.
Guess I have to be keeping working around the problem.
Thanks anyway.
Fret

 

[Fret4u]

Hey Pascal, it's me again. I figured it out ! The following was the problem. I have a form "invoice" and a form called "VAT" With VAT I fill the view VAT with data. And the field VAT% in the form invoice retrieves it's data from the view VAT. Now I let the view VAT fill its data from the form "invoice" field "VAT%" and the keywords appear in the dialog box. When I add a value the new value imeediatly shows up in the view VAT and in the keyword list.
But I don't use this, because in the keywordslist all entry's appear. So if I have 10 invoices with VAT% 19 and 5 with VAT% 6 my keyword list shows 10 times 19 and 5 times 6. It should only show once 19 and once 6. But every time I add a new invoice the new kayword is added to the list. So if my 11 th invoice would have a VAT% of 19 the keywordslist would show 11 times 19. I tried to solve this with @Unique and "Distinct" but then I get error "parameter not correct". And there my knowledge ends. So I struggle on the old way...or do you have a solution ?

How does @PickList work ? I didn't get this working either.

Last question with which you might help me: if I have a default value for a field and this field is editable, I would like to manage to have the value in this field selected so if I wnat to change it I simply can type a key and it changes. eg: the VAT is default 19. When I add a new invoice and I get to that field I manually have to delete the value (either manually select and delete or with backspace or delete key). But what I want now is that when I get to that field the value is automatically selected. If I want to change it I simply type "2" and the value 22 pops up. or I type "6" to select the value "6". If I don't want to change the value I should only simply have to type the "tab" key to jump to the next field. You know waht I mean ? Any suggestions ?

 

[pmonett]

For the VAT view, I think you would be better off categorizing the values in the first column (which ensures uniqueness), and using a @DbColumn to retrieve the list. Either change your current view if it does not bother you, or make a specific (hidden) lookup view for the job.

@Picklist is basically a @prompt tailored for presenting users with views to choose data from. The idea is that the function presents a view, the user chooses a doc (or several), and the function returns one field (or column) value.
The NAME version shows how easy it is to retrieve user name choices from the NAB with it. For the rest, @Picklist is merely a @DbLookup that displays a view. You just need to try it a few more times.

For your selectable VAT field, I suggest you make it a Dialog List keyword field. That should give you the closest approximation to your request, since typing into a DLK field will most often autocomplete with existing values. Try various Property settings until you get the one that suits you the most. Do not forget to save the form every time you test.

Pascal.

 

[Fret4u]

That's it...categorizing...that I didn't think about this earlier...but how do I hide a view (hidden lookup view) ?

 

[pmonett]

Just put its name between round brackets.
Thus, VATview should be named (VATview).

The brackets count in naming the view - you'll have to change your code to take that into account as well.

So, your lookup will be something like

@DbColumn("":"";"":"";"(VATview)";1)

Pascal.