Handling look-ups in Simpol

While I've conceptualized some approaches, I was wondering what others 
have decided.  (Maybe there is some documentation that I have missed.)

The Simpol tables don't have calculated fields, so I presume that this 
includes that lookups or validations via a look up to another field are 
not part of the table but will have to be handled via a routine attached 
to the field on a form.  Instinctively, I am thinking that every field
will have to have both an onfocus(but maybe this is only necessary with 
a required field) and an onchange function that in part will handle this.

It seems obvious to me that how the Desktop is handling dates does 
something like this but even that is not quite working correctly.

(When there is a table view, I gather that this will in actuality be a 
form that "looks like" a table view, so it to will need to have this 
built in, which kind of begs the question that the table should have 
this built in -- but I've lost that battle. And I gather actually that 
record view is actually just a special form view that "looks like" a 
record view -- so the same issue is here, too. But I don't understand 
how that gets worked out when the validation lookup is not really part 
of the table. And this means that the Desktop will always screw up 
routinized fields.)

It really doesn't seem like each one of us should have to invent and 
reinvent this wheel.  So perhaps there should/could be an example.

Let's consider something like the ADRB table that comes with the Desktop 
and the field "ACountry". Let's suppose, we don't want our end-users
typing in the country (E.g. sometimes "USA", sometimes
"U.S.", sometimes 
something else with typos) but rather we want the field to be chosen 
from another table that has the standardized spellings and names of 
countries.

Let's assume that we aren't going to deal with what we previously call a 
"required" field, but that either .nul or "something" from
the Table 
"Countries" is acceptable, but anything else will make the user choose

from a list from a separate dialog (and hence, form).

I'm kinda surprised that there has been no response to this at all.

Kromkowski wrote:
> While I've conceptualized some approaches, I was wondering what others 
> have decided.  (Maybe there is some documentation that I have missed.)

Not yet.

> The Simpol tables don't have calculated fields, so I presume that this 
> includes that lookups or validations via a look up to another field are 
> not part of the table but will have to be handled via a routine attached 
> to the field on a form.  Instinctively, I am thinking that every field
> will have to have both an onfocus(but maybe this is only necessary with 
> a required field) and an onchange function that in part will handle this.

You could add an onchange or onlostfocus to each field that needs it, 
which I would do in the case of fields that affect the value of a 
calculated field, but only if you are showing the calculated field on 
the form. I would otherwise only attach an onsave event and handle the 
calculations in there. If you need to do constants/default values, you 
can also create an onnewrecord event handler, and do them in there.

> It seems obvious to me that how the Desktop is handling dates does 
> something like this but even that is not quite working correctly.

That may have issues in the algorithm, I will have to look. The real 
work is not done by Desktop, but by the underlying dataform1 object and 
related objects.

> (When there is a table view, I gather that this will in actuality be a 
> form that "looks like" a table view, so it to will need to have
this 
> built in, which kind of begs the question that the table should have 
> this built in -- but I've lost that battle. And I gather actually that 
> record view is actually just a special form view that "looks
like" a 
> record view -- so the same issue is here, too. But I don't understand 
> how that gets worked out when the validation lookup is not really part 
> of the table. And this means that the Desktop will always screw up 
> routinized fields.)

It isn't that you have lost the battle, there *will* eventually be a 
version of the database that has this level of access built in, but we 
started with the lower level access, and people will be able to choose 
which API they wish to use once we add the other one. As for table view, 
yes, it will, like record view, be a form that contains a grid control 
and which acts like a form. I don't actually use the dataform1 stuff for 
these, they are special cases currently and are specially coded and more 
lightweight than a general purpose data-aware grid might be. As for 
handling things like onsave, and onrecordnew, I don't think they are yet 
in record view, but they will be.

> It really doesn't seem like each one of us should have to invent and 
> reinvent this wheel.  So perhaps there should/could be an example.

As soon as I can get the remaining bits done, I will be building an 
example of this.

> Let's consider something like the ADRB table that comes with the Desktop 
> and the field "ACountry". Let's suppose, we don't want our
end-users
> typing in the country (E.g. sometimes "USA", sometimes
"U.S.", sometimes 
> something else with typos) but rather we want the field to be chosen 
> from another table that has the standardized spellings and names of 
> countries.

You can use a combobox. The content of the combobox can be drawn from a 
separate table called country. You can even show the name of the country 
using one field, but assign the country code from another field to the 
bound field in ADRB.

> Let's assume that we aren't going to deal with what we previously call a 
> "required" field, but that either .nul or "something"
from the Table 
> "Countries" is acceptable, but anything else will make the user
choose 
> from a list from a separate dialog (and hence, form).

You could do this with a combobox like I describe above, but allow them 
to type, and in the onlostfocus, if it isn't valid, you could pop up an 
equivalent to a request 20 for example (or the drill down). I haven't 
sat down and written either yet, but both are fairly easily implemented 
in SIMPOL, using a dialog, a listbox, an edit control, and a couple of 
buttons.

I will eventually add one of these, it is just a matter of time. If you 
want to start on one, and publish the code here, we can all participate 
in its creation (start maybe with a design discussion) and it might 
prove a useful learning experience for everyone.

There are aspects to your reply that are kind of disheartening.

1.That you have not sat down and written one either.

2. This comment:  "there *will* eventually be a version of the database 
that has this level of access built in, but we started with the lower 
level access, and people will be able to choose which API they wish to 
use once we add the other one."   What!  There will be another version 
that actually has the capacities that one might have expected?

3.  That the Desktop doesn't actually use dataform1 stuff.

As to me starting the process, for this learning exercise.  Isn't there 
something pathetic that it would be me, the lawyer outside the normal 
channels of programming, to begin?

What I can tell you is that while conceptually I understand the 
combobox, all that I am able to do with the Desktop form designer is 
create a form that completely freezes the Desktop (Error retrieving 
field value for assignment ) AND one in which nothing shows up in the 
dropdown!

So maybe this exercise could start with a step by step of creating a 
form that has one combobox (dropdown) that shows values from a selected 
field from a selected Table.  I can't seem to figure out that basic task.

JDK

OK, well I was able to create a form that has a whole different kind of 
error (valid2.sxf).

But why don't we start with this.

TableA:

Two fields.  "Field1"  an interger,  "FromTableB" a string.

TableB:

One field.  "BField" a string.


The aim is as follows:

We want "FromTableB" in Table A to only have values
from BField in Table B.


This is an entirely simple matter to do in SB2 or SB3, but that
is because it is done at the file/table level.  So if we create
a form in the old SB2 or SB3 form designer, its a simple matter of
opening the form.  But to this simpleton, I don't think you can
actually create a form ("valid.sxf") that you could simply open
using:

f =@ opendataform1(filename, error=e, errortext=errtext)

that will accomplish this task.

This is one of the most basic kinds of things that any database 
application with a form interface (I'm putting aside the number 
crunching kinds of applications) is going to need to have.