Methods and Value List

Classic Servoy
1

Here’s the scenario, in case it helps: on a Contacts form, I want the user to enter the Company. If the company exists, it will link using the typeahead. If, however, the user enters a value not found in Companies, the method will go create that company, and bring back the appropriate key.

First, I’ve run into an issue (or my own incompetence) that triggers the method at the wrong time (I’ve posted that elsewhere). But let’s assume for now that the very cooperative user triggers the method after editing the company name.

The company name is actually entered into the k_comp (key, company) field, with a value list that show the company name, but returns the company key.

My method (pasted below) checks to see that they’ve chosen a valid company. If not, the method grabs the new company name, goes to the company form, creates a company, gives it the name the user entered, grabs the new key, comes back, and links the contact to the new company.

All this seems to work, except that the k_comp field now displays the company key, not the company name. If I restart the solution, or if I go into layout and back, it will display the company name again.

Is there some sort of “refresh” command I could use, or am I perhaps setting variables and fields in some way that messes with the display?

Here’s the Method (general efficiency and best practice tips are welcome, too!):

if ( !people_to_companies.pk )
if ( k_comp )
{
{
var a = k_comp
forms.companies.controller.newRecord()
forms.companies.company_name = a
var a = forms.companies.pk
k_comp = a
}
}

2

Hi,

You can’t do what you want, because of the nature of the valuelist you’ve chosen. Here’s why: if you have the valuelist display one value and return another, and the user types in a value that’s not on the list - what will be returned into the key field? SO - the behaviour of the field is to try to store the name of the company.

To do this:

APPROACH #1:
Set the value list to “custom values” and then filling the value list with a SQL query and adding a “New…” entry to the top of the list.

APPROACH #2:
Make the company key a LOOKUP field, and then have the onDataChange method of the field see if the key is blank, then add the record.

Hope this helps,

Bob Cusick

3

Bob,

I like approach 2, and will probably end up resorting to that.

However…

If the user types a value that is not on the list, the field (which I can display elsewhere on the form) accepts that value as the “key”. The intent of the method is to correct that, by creating the company and getting the correct key. It does, in fact, work nicely, except that until the form is “refreshed”, the new key shows in the field, instead of the company name.

The crux of the matter is that when the method puts a valid key in the key field, the key is displayed rather than the name, until I “refresh”, either by going into design mode or restarting the solution. Is there a “refresh” command of some sort? Is there some reason that even though the values are demonstrably correct, the display is not?

(i.e., I can see that the key is set to “123” and company “123” is “ABC Company”, but the typeahead field displays “123” until I restart the solution or toggle Design mode, at which point it properly displays “ABC Company”.)

4

Set a DIFFERENT field - NOT the popup field! Then call: application.updateUI

Should work fine.

Bob

5

I tried just the application.updateUI, and that didn’t do it.

How would I set a ‘different’ field? I’m setting ‘k_comp’ on the form, under ‘selectedrecord’. Is there another way to set that field? I wasn’t aware that fields addressed in this way were in the context of their display properties on the form.

6

Hi Cain,

I make forms called “_DEV_formName” or “_DATA_formName” for each table in my soluton. Because they are based on the same base tables as my other forms (i.e. “customers_data_entry” and “_DATA_customers” are both based on the “customers” table) - you can just update the data using: forms._DATA_customers.field = value.

:)

Hope this helps,

Bob

7

It might…

Do I have to create a relationship to ensure that I’m updating the same record in both forms, or are found sets by table?

I’ll experiment, but I’d bet you know this off the top of your head…

8

Found sets are by TABLE unless you specifically check the “useSeparateFoundSet” property on the form.

That’s how this works. :D

Bob

9

Bob,

First off, SWEET! I didn’t realize that found sets went with tables, nor did I see that form attribute. IMHO, neither FM6 nor 7 got that right, but I really think the Servoy way makes sense. (Of course, right?)

However…

Here’s my new Method. Still not displaying correctly.

if ( !addresses_to_companies.pk )
if ( k_comp )
{
{
var a = k_comp
forms.companies_form.controller.newRecord()
forms.companies_form.company_name = a
var b = forms.companies_form.pk
forms.addresses_list.k_comp = b
application.updateUI()
}
}

10

Cain,

It’s SORT OF like FMP7… but WAY BETTER! :D

The problem is with your code - you’re putting the { } in the wrong place. Try this:

if ( !addresses_to_companies.pk )
{
if ( k_comp )
{
var a = k_comp
forms.companies_form.controller.newRecord()
forms.companies_form.company_name = a
var b = forms.companies_form.pk
forms.addresses_list.k_comp = b
application.updateUI()
}
}

The first bracket comes after the first “)” and before the second “if”.

Cheers,

Bob

11

Yup, that was wrong in the code… but it didn’t change anything.

I’ve attached some screen shots in a Word doc.

  1. Entered a company in the “k_comp” field, who’s name wasn’t in the Company file.

  2. Ran the Method to create a new company, and set the new company key, using the address list form instead of this one (see method pasted below). On the form now, you can see the key value in the k_comp field, even though the valuelist says to display the name.

  3. Went into Designer mode, then returned. Field displays correctly.

Method:

if ( !addresses_to_companies.pk )
{
if ( k_comp )
{
var a = k_comp
forms.companies_form.controller.newRecord()
forms.companies_form.company_name = a
var b = forms.companies_form.pk
forms.addresses_list.k_comp = b
application.updateUI()
}
}

12

Not sure exactly what the difference is but I am using:

databaseManager.refreshRecordFromDatabase(foundset,recordIndex); // refreshes the record

whenever I know something has posted to the database (via stored procedure, etc) and want to make sure the user can see the changes. It works very well when I have used it.

John

13

Well… It didn’t work, but it did manage to give the list a different found set than the form, in spite of what I’d learned previously in this thread.

14

One thing I noticed in my post but you probably figured it out was that earlier in my method the current record index gets established as a variable (recordIndex) and then that is the record that gets refreshed. As mentioned I use it in a little different way (refreshing a record after a stored procedure updates a record) but certainly in that way it works very well. Sorry it didn’t help in your solution.

John

15

So how did you establish the found set earlier?

If the found set is consistent for the table, shouldn’t the found set change on the form, too?

This might actually work, but I’m concerned about the rather freaky results I got when I tried it. My controller went a bit nuts, too, and the max record number wouldn’t change after I tried this.

Bottom line, a typeahead should display per the configuration of the value list, regardless of whether the value is set by a user or a method, shouldn’t it?

Why do I feel like I’m having to “trick” Servoy into behaving consistently?

16

In my particular situation where I have used this feature, my foundset is done by an SQL query and generally speaking that is how I manipulate my foundsets. Basically ours is a ‘production’ database in which we pick up the ‘orders’ on a daily basis, the lab people use those orders to print labels, etc. and then the MDs sign off on the cases in the afternoon. Once a case is signed off it is omitted from the found set. If I want to reestablish the foundset I do it via a query. Works fast and accurately. In the situation described. a case is missing some information at the time the original stored procedure brought it into our database. So a stored procedure is run on the current record to find any updated information. The ‘refresh’ command is then run on that current record to show the new information after the update stored procedure is run as otherwise it is not seen by the user. In this situation though I’m not creating a new record however, simply updating and refreshing an existing record.