Get Underlying Table for a Form at run-time

How do I?
1

Is there a function in the Five API that will give me the name of the underlying table for the active form?

Use-Case: I have a Reports table. One of the fields is Seq, a sequence number that I want the records listed in. If I change the sequence number while editing a record, I want to (on saving the record) run a function to re-sequence the record. For example, if I have records with Seq numbers 10, 20, 30, 40 and I want to re-sequence the first report I would change the Seq number from 10 to 21, which should make the report now appear second in the list order. Now I want the sequence numbers “recalculated” so instead of 20, 21, 30, 40 they are renumbered to 10, 20, 30, 40 again.

I believe I already know how to do the re-sequencing, as I do this in my Access applications. In the ReSequence function, I need to know the underlying table name so I can build a SQL statement. I know the form name by using actionID(), but don’t know how to get the underlying table name for actionID().

Can you help with this? Thanks…

2

Hello,

Are you able to make a function based around the table that is used for that form which runs each time the resequencing is required.

Is there a particular reason you would need to find the table name if it is the same table every time?

Thanks,
Riley.

3

Thanks for the quick reply. I wanted to make the function more generic so it would work for any table that happens to have a Seq column. It seems inefficient to have to write a different function for every form/table.

If there isn’t a way to find the underlying table for a given form, perhaps I could save a CurrentTable variable on form open event? Unfortunately this still isn’t perfect because I don’t want a different function for each form. Maybe a switch section that would have a case for each form.

Best solution would be some type of metadata that returns the underlying table for the current form.

Thanks…

Ron Mittelman

4

I created a FormShow function which, using a SWITCH of the actionID(), can set a variable for the underlying table if there is no metadata which returns this.

If I do it this way, I also need to know the order of events on forms, because some forms also need to run a function to init their sub-form pages. So I would want to run the FormShow function before running the InitSubforms function, so the underlying table name would be known when running InitSubforms.

Please let me know the event order, and perhaps which events would work for setting the variable, so I can address this.

Thanks…

5

Hello,

Given that the data source is a table, query, or connection, you can use the following code to determine the underlying table name.

var action = five.getFive().getActionByName(five.actionID())

var form = five.getFive().getForm(action.key())

var datasource = form.getDataSource()

Where datasource is the table.

Hope this helps,

Thanks,
Riley.

6

Thanks so much for this reply. I will try it out immediately. This will save a lot of trouble if it works.

If this does work, you can ignore the prior question about order of events, because I won’t need a separate function just to save the name of the underlying table.

7

I added a function called ReSequenceTable with your code:

    var action = five.getFive().getActionByName(five.actionID())
    var form = five.getFive().getForm(action.key())
    var myTable = form.getDataSource()

    // let myTable = 'Reports';
    five.showMessage('Table: ' + myTable);

I call this function from the SetupReports form’s On Complete event.
When I execute the code, I get this:
image

So I tried

    var action = five.getFive().getActionByName(five.actionID())
    var form = five.getFive().getForm(action.key())
    var myTable = form.getDataSource()
    var tableName = myTable.Name

    // let myTable = 'Reports';
    five.showMessage('Table: ' + tableName);

and now I get this:
image

I will admit that I took a guess with myTable.Name. I also tried myTable.name, but both said “undefined”.

I really just want the name of the underlying table for the purpose of building a SQL statement against that table.

8

Hello,

If you change the line

var myTable = form.getDataSource()

to

var myTable = JSON.stringify(form.getDataSource())

It should then output the table name with a corresponding ID, let me know how you go with this and add a screenshot of the output.

Also, remove the tableName row before doing this.

Thanks,
Riley.

9

Here is the entire contents of my client-side function. I removed the part that calls the server-side function.

function ReSequenceList(five, context, result)  {

    var action = five.getFive().getActionByName(five.actionID())
    var form = five.getFive().getForm(action.key())
    var myTable = JSON.stringify(form.getDataSource())
    five.showMessage('Table: ' + myTable);
    
    let myParms = {
        Table: myTable
    };
    return five.success(result);
}

Here is the error message returned:

image
image1500×470 77.4 KB

So something is not working properly. Any ideas? Thanks…

10

Hello,

Would you be able to send through your application with this function included in it and I can have a look.

Thanks,
Riley.

11

Here is the FDF file
BrandeisConejo-20240919-165215675469821.fdf (5.9 MB)

By the way, when I removed the JSON.stringify part of the statement, it worked without generating the error. So the problem seems to be with the JSON part.