# Get Underlying Table for a Form at run-time

**URL:** <https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458>\
**Category:** How do I?\
**Created:** [September 13, 2024, 11:40pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458 "2024-09-13T23:40:23Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![RMittelman](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/rmittelman/32/447_2.png) [@RMittelman](https://www.five.org/u/RMittelman)\
**Post date:** [September 13, 2024, 11:40pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/1 "2024-09-13T23:40:23Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![RileyAnderssen](https://avatars.discourse-cdn.com/v4/letter/r/5f8ce5/32.png) [@RileyAnderssen](https://www.five.org/u/RileyAnderssen)\
**Post date:** [September 15, 2024, 9:50pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/2 "2024-09-15T21:50:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![RMittelman](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/rmittelman/32/447_2.png) [@RMittelman](https://www.five.org/u/RMittelman)\
**Post date:** [September 15, 2024, 11:33pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/3 "2024-09-15T23:33:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![RMittelman](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/rmittelman/32/447_2.png) [@RMittelman](https://www.five.org/u/RMittelman)\
**Post date:** [September 17, 2024, 3:50am UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/4 "2024-09-17T03:50:31Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![RileyAnderssen](https://avatars.discourse-cdn.com/v4/letter/r/5f8ce5/32.png) [@RileyAnderssen](https://www.five.org/u/RileyAnderssen)\
**Post date:** [September 18, 2024, 10:16pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/5 "2024-09-18T22:16:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![RMittelman](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/rmittelman/32/447_2.png) [@RMittelman](https://www.five.org/u/RMittelman)\
**Post date:** [September 18, 2024, 10:53pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/6 "2024-09-18T22:53:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![RMittelman](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/rmittelman/32/447_2.png) [@RMittelman](https://www.five.org/u/RMittelman)\
**Post date:** [September 18, 2024, 11:19pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/7 "2024-09-18T23:19:32Z")

</div>

I added a function called ReSequenceTable with your code:

```auto
    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](https://canada1.discourse-cdn.com/flex030/uploads/five/original/1X/4448f04e2f286fd7ca4e951d5cbb0248990f2122.png)

So I tried

```auto
    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](https://canada1.discourse-cdn.com/flex030/uploads/five/original/1X/1b1d7b290c2310bea6e806025d5803b8c8cf5e97.png)

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.

---

<div class="post-metadata">

**Author:** ![RileyAnderssen](https://avatars.discourse-cdn.com/v4/letter/r/5f8ce5/32.png) [@RileyAnderssen](https://www.five.org/u/RileyAnderssen)\
**Post date:** [September 18, 2024, 11:33pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/8 "2024-09-18T23:33:10Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![RMittelman](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/rmittelman/32/447_2.png) [@RMittelman](https://www.five.org/u/RMittelman)\
**Post date:** [September 19, 2024, 2:52am UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/9 "2024-09-19T02:52:06Z")

</div>

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

```auto
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](https://canada1.discourse-cdn.com/flex030/uploads/five/original/1X/a3c8afb83b1c97dd218bd25e2668aab27d3b1fb1.png)  
So something is not working properly. Any ideas? Thanks…

---

<div class="post-metadata">

**Author:** ![RileyAnderssen](https://avatars.discourse-cdn.com/v4/letter/r/5f8ce5/32.png) [@RileyAnderssen](https://www.five.org/u/RileyAnderssen)\
**Post date:** [September 19, 2024, 5:31am UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/10 "2024-09-19T05:31:11Z")

</div>

Hello,

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

Thanks,  
Riley.

---

<div class="post-metadata">

**Author:** ![RMittelman](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/rmittelman/32/447_2.png) [@RMittelman](https://www.five.org/u/RMittelman)\
**Post date:** [September 19, 2024, 4:57pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/11 "2024-09-19T16:57:22Z")

</div>

Here is the FDF file  
[BrandeisConejo-20240919-165215675469821.fdf](https://www.five.org/uploads/short-url/dIGippte352B3JxByk7lIEDiCVg.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.

---

<div class="post-metadata">

**Author:** ![RMittelman](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/rmittelman/32/447_2.png) [@RMittelman](https://www.five.org/u/RMittelman)\
**Post date:** [October 23, 2024, 5:48pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/12 "2024-10-23T17:48:38Z")

</div>

So I have this function called ReSequenceList. Its purpose is to, well, re-sequence a list. This is done by updating the underlying table and modifying the Seq column in all records.

For this to work, I need to find the name of the underlying table so the function can be generic for any table with a Seq column.

I am having trouble getting this to work. I’ve trimmed the client function so I can test getting the underlying data source:

```auto
    var action = five.getFive().getActionByName(five.actionID())
    var form = five.getFive().getForm(action.key())
    var myTable = form.getDataSource();
    // var myTable = JSON.stringify(form.getDataSource());
    five.log("action: " + action);
    five.log("form: " + form);
    five.log("table: " + myTable);

```

If I un-comment the statement using JSON, I get a long error message about circular parsing on Json object. Otherwise, I get the following results, which are not helpful:

 ![image](https://canada1.discourse-cdn.com/flex030/uploads/five/original/1X/664fa525ad2e4fb4dc38357a0679e5d44d628b32.png)

So is there any way to obtain the name of the underlying table reliably?  
In case you need it, here is the fdf file:  
[BrandeisConejo-20241023-174717735239134.fdf](https://www.five.org/uploads/short-url/9W5ErvnWBV12A47D4n15eQYGVK9.fdf) (5.9 MB)

It’s been over a month with no answer so far. Can someone help with this?

---

<div class="post-metadata">

**Author:** ![RMittelman](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/rmittelman/32/447_2.png) [@RMittelman](https://www.five.org/u/RMittelman)\
**Post date:** [October 25, 2024, 9:35pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/13 "2024-10-25T21:35:34Z")

</div>

I have a work-around for this issue, which is to put a switch block near the top of the function. Define a myForm variable, assign ActionID() to it, then switch on myForm to assign a table.

If you cannot find a way to “calculate” the underlying table name based on actionId(), then this work-around will suffice.

---

<div class="post-metadata">

**Author:** ![RileyAnderssen](https://avatars.discourse-cdn.com/v4/letter/r/5f8ce5/32.png) [@RileyAnderssen](https://www.five.org/u/RileyAnderssen)\
**Post date:** [November 14, 2024, 9:38pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/14 "2024-11-14T21:38:57Z")

</div>

Hello Ron,

Glad you found a work around for the issue!

There is currently no way to get the underlying table at run time, however, as you found, there are a few work arounds to solve this issue.

Another potential work around would be determining the actionID of the current form and then using an if statement to determine which table is associated with that form.

Thanks,  
Riley.

---

<div class="post-metadata">

**Author:** ![RMittelman](https://yyz2.discourse-cdn.com/flex030/user_avatar/www.five.org/rmittelman/32/447_2.png) [@RMittelman](https://www.five.org/u/RMittelman)\
**Post date:** [November 14, 2024, 11:09pm UTC](https://www.five.org/t/get-underlying-table-for-a-form-at-run-time/458/15 "2024-11-14T23:09:56Z")

</div>

Thanks for the reply, Riley. It seems that I had already thought about that, and already have a function which includes Switch logic to specify the table. This function actually creates a variable. However, according to Jo those variables are not available for server-side function execution such as Do events. Therefore, the work-around seems better.

Thanks…

_Ron Mittelman_