[Challenge] Week 2/28/05: Getting form and field names

by David Workman
Data Mosaic

(Answer posted here.)

This week I need some help! I have this solution that I'm working on that transfers data back and forth from Servoy to FileMaker. The tricky parts are all done (like the FileMaker XML processing plugin for Servoy). Now I need to finish the interface (as a module) so I can add FileMaker data transfer to any solution I want.

One tab panel is for lining up the fields from the SQL side of things with the fields from the FileMaker side of things. Notice I have a combobox field and a list for both. The combobox has a value list showing all the layouts/forms and the list under each combobox lists all the fields for the layout/form selected.

On the right you see the FileMaker specific area. As it turns out, FileMaker has XML calls that will return the layout names in a database and the fields on a particular layout. Using my spiffy plugin, the button to the right of the combobox fills the value list with FileMaker layout names (the database has already been chosen on a previous tab panel). The code for this is:

//clear related rows
forms.lvl2_bd_field_list.controller.loadRecords(bridge_to_fmfield);
bridge_to_fmfield.deleteAllRecords();
fm_layout = '';

//populate 'layouts' value list with layout names in this solution
var layoutsARY = plugins.fm6webcompanion.getLayoutNames(
    forms.lvl1_connection_detail.url,
    forms.lvl1_connection_detail.database_selected,
    '',
    forms.lvl1_connection_detail.connection_to_dbs_database.db_password);

application.setValueListItems('layouts', layoutsARY);

When you select a layout from the value list, a method is called that looks up all the fields on that layout:

//clear related rows
forms.lvl2_bd_field_list.controller.loadRecords(bridge_to_fmfield);
bridge_to_fmfield.deleteAllRecords();

//return columns from selected filemaker layout
var fieldInfoARY = plugins.fm6webcompanion.getFieldInfo(
    bridge_to_connection.url,
    bridge_to_connection.database_selected,
    fm_layout,
    bridge_to_connection.password_selected);


//create related records for each column name
for ( var i = 0; i < fieldInfoARY.length; i++ )
{
    bridge_to_fmfield.newRecord();
    
    //field name
    bridge_to_fmfield.f_name = fieldInfoARY[i][0];
    
    //field type
    bridge_to_fmfield.f_type = fieldInfoARY[i][1];

    //order
    bridge_to_fmfield.f_order = i + 1;
}

//go to first related record
bridge_to_fmfield.recordIndex = 1;

Now for this week's challenge. Come up with the same functionality for the Servoy specific part of the layout:

1. Write a method that fills the combobox value list with all of the form names in the current Servoy solution.
2. Write a method that returns all the column names of the selected Servoy form.
3. BONUS challenge: return the column types for each column name as well.

Post your answers below. By the end of the week I may figure this out myself and post how I did it....

Have fun!