Set Form Fields from Database Resource - Not Working

[tom.smith]

Hi

I've been trying to work out over the last day or so why my the form fields on my form aren't being set from a database resource call - anyone have any pointers?


- I have my database connection configured
- I have my database resource configured using native SQL (SELECT column1, column2 FROM table WHERE column1='sometext'). Non-table operations (I only want to return 1 row)
- Resource fields have been set in the database resource to match the columns within the database
- The database resource has been added to the Business View
- The business view has been added to the form
- A "before form" event has been configured to run script1
- script1 contains a fetch command and some logging for debug purpose
- Form fields have been configured and field controls placed on the form
- Mapping has been configured within the form which map the database resource fields to the form fields

According to the execution logs, when you run the form, the 'before form' event is triggered. The SELECT statement is issued to the database, and $FOUND returns "Y" - which indicates that the row has been returned.

However, the field controls on the form are blank, and the debug output (log field1 etc) is blank, which indicates that the form fields have not been set.

Am I missing any steps? I've been through the training handbook several times, been reading the help topics and have run out of ways to check what's happening. As far as I can make out the SELECT statement is returning data, but either I'm not populating the forms fields correctly, or the system isn't doing it.

Any help would be very much appreciated.

Thanks in advance

Tom

[Jon]

Tom,

It all sounds fine from your description. You might try clicking the debug option in the Database Resource - this will show you the details of the SQL execution. If you still can't get it working, export the form and all related elements and send it to support (support@ebasetech.com).

Regards
Jon

[tom.smith]

Hi Jon

Thanks for your quick reply.

I'd been using the debug option for the database resource - sorry, should have mentioned that. That's how I was able to tell that the SELECT statement was being issued correctly.

I've now got part of this working - sods' law after all that time that 10mins after the post I get a breakthrough

I am now able to get information to the form from the database - I had to rename the resource fields in the database resource so that they matched the actual database columns, and ensure that persistence was checked. If I try and use form fields that have different names than the internal database column names, and uncheck persistence, it breaks again.

According to the handbook that I've got, I should be able to use different names, I just have to ensure that persistence isn't checked - can you / anyone confirm this?

I have 1 form field that has a different name than it's corresponding database column name - intentionally. If I try and use this in the WHERE statement, I get an error:

Field substitution error, reference to field field1 which has no mapping, source databaseResource1

The field is mapped in the calling form (field1 > field2).

I'm going to continue to see if I can get the resource to use this mapped field, if not based on what I have gotten to work this afternoon, I should be able to get it working by renaming the form field to the same as the resource field.

[tom.smith]

Right, got it working. Originally I'd set up all of the form fields etc manually.

After removing the form fields from the forms, and then importing them from the database resource, I can now interact with the resource in every way that I wanted to. I must have missed a step in the manual creation of everything - quite what, I don't know but at least it works and I have a method to use first time in the future.

Thanks for the quick support, it's always good to have this type of communal support.

[Jon]

Tom,

The resource field names in the Database Resource must be the same as the database column names when the persistent flag is checked - this is a rule. Also, the persistent flag must be checked if you want to read/write the column to/from the database. The occasion that you want to have persistent unchecked is when you just want to pass a value into the resource from the form for use in the WHERE clause e.g.

Code: Select all

select * from MYTAB where CREATION_DATE >= &&YESTERDAY

Then YESTERDAY is marked non-persistent in the Database Resource and mapped to a form field.

The form field names can be anything you choose and then you map these to the Database Resource fields. So the whole thing is a bit like this:

Form Fields <-1-> Database Resource Fields <-2-> Database

Where 1 are mappings that you configure, and form field names and resource fields names can be different. You configure these using the Resource Mappings Panel from the toolbar at the top of the form editor.

And for 2, resource fields must be the same name as database columns. Only applies when persistent is checked.

I didn't follow your remark about the field being mapped in the calling form. I don't think that mapping has got anything to do with these resource mappings.

I don't know if this helps but if you're just processing a single table, you can get the system to do all this stuff automatically. Click the Database Schema Wizard icon at the top of the Database Resource Editor to import a table from the database, then in the form, click the Import fields from external resource icon at the top of the Fields Panel.

Regards
Jon