Sort Items in List based on Field Value

Hello,

Glad to hear you got it working.

Regarding the header field, Five looks for the first field included within the form list in which is a text field. This field is then used as the header. Do you have the Seq field set as a text field?

Feel free to send through the application with this change and we can investigate it further.

Thanks,
Riley.

Thanks Riley. Changing Seq to _Integer seemed to work with the display.

Now just need to get the re-sequencing to work.

Here is the event on the SetupReports form:
image

Here is the code:

function ReSequenceReports(five, context, result)  {
    let sql = `SET @new_seq = 0; UPDATE Reports SET Seq = (@new_seq := @new_seq + 10) ORDER BY Seq;`;
    let tableName = context.Transactions[0].Values.TableName;
    result = five.executeQuery(sql, 0)
    five.log('table: ' + tableName);
    return five.success(result);

At one point, I could see the proper TableName in the log, but now I just get the following error:

TableName is a valid form field, but not a database table field, as described in Jo’s earlier comment. So I’m guessing it should be in the Transactions collection, right? Not sure why it is timing out. Here is my fdf file:
BrandeisConejo-20241117-232826544318291.fdf (7.3 MB)

The problem seemed to occur after I put in the code to get tableName from context.Transactions.
Thanks for the help on this.

UPDATE:
I commented out the “let tableName = …” and the “five.log…” statements and re-ran the application. I kept getting the “Failed to save record…” message. I closed all browser tabs pointing anywhere near your website. Then I re-logged in to the website using the control ID. Now the application worked to re-sequence the reports.
Then I un-commented the above-mentioned lines of code and re-ran the application. Now I get the following error:

Very curious. I hope you can help me figure this out. If necessary, I can go back to the “old” method of using On Complete, letting that call a server-side function to do the update. Would that work better?

Hello,

testing-20241118-012506839334127.fdf (3.4 MB)

I have made a small adjustment to the testing FDF I sent earlier, if you run with the inspector, you are able to see all values returned with the Transactions array, unfortunately, the table name is not one of them, however, it does return the actionID so you could potentially obtain the underlying table to the corresponding actionID if it is known.

The actionID value can be obtained by:

context.Transactions[0].ActionID

Let me know how you go!

Thanks,
Riley.

Thanks for the update Riley. I made some changes to the latest testing app.
I added a TableName field to the form without a data source. I added a function triggered by On Show event, which filled in the TableName form field per Jo’s example earlier in the thread. then I modified the other function as follows:

function GetFieldValues(five, context, result)  {
    five.log(JSON.stringify(context.Transactions));
    five.log("Number: " + context.Transactions[0].Values.NumberOne);
    five.log("Name: " + context.Transactions[0].Values.NameOne);
    five.log("ActionID: " + context.Transactions[0].ActionID);
    five.log("TableOneKey: " + context.Transactions[0].Values.TableOneKey);
    five.log("TableName: " + context.Transactions[0].Values.TableName);
    return five.success(result);
}

So when I ran the app with inspector, I did not see the new field logged. I thought it would be there, because it IS a form field just like the others.

What I did see was the first item in the log, which looks to be JSON:

[{"ActionID":"TableOneForm","Key":"014e997a-c0cc-458b-b617-f2aa896c5d3a","Type":"UPDATE","VersionKey":"d3a22040-a561-11ef-98d7-ee2babd81c14","Values":{"NameOne":"Jacky","NumberOne":"2","TableName":"TableOne","TableOneKey":"014e997a-c0cc-458b-b617-f2aa896c5d3a"}}]

I see from the function code that is the Transactions collection from context. It’s unclear why it appears in the transactions collection, but it doesn’t appear to be logged, even though I explicitly told it to log after the other items. See the altered function code above. Can you clear that up?

As mentioned earlier, I can use On Complete to load a context then call a server function, but it seems like the Transactions object should have all of the fields when I try to log the values. Did I do something dumb in the function and just can’t see it?

Alternatively, I can use Jo’s code directly in the Do Complete function:

function OnShowForm(five, context, result)  {
    five.field.TableName = five.getFive().getUserAction(five.getAction(five.actionID()).key()).getForm().getDataSource().dataSourceId();
    return five.success(result);
}

Without actually setting a form field like she did, couldn’t I use the statement in this function (not the whole function itself) to set a variable based on the ActionID from the Transaction? It’s unclear whether all the parts of the above statement are usable in a server-side function.

Thanks…

Hello,

Would you please be able to send through the FDF with the modifications you made (the testing FDF) and I will have a quick look as to why it is not logging correctly.

From what I can see, it looks like what you have done should work, so I will need the FDF to double check. If we can’t get it to work using the Transactions method, then yes you may have to consider using Jo’s method to get the table name.

Thanks,
Riley.

Thanks for the reply Riley.

Perhaps you can provide some clarification. Is calling a client-side function (via On Complete event) which then calls a server-side function after building context functionally the same as calling a server-side function via Do Complete event? It seems like the former would give more control.

Here is the server-side function called by Do Complete:

function ReSequenceReports(five, context, result)  {
    let sql = `SET @new_seq = 0; UPDATE Reports SET Seq = (@new_seq := @new_seq + 10) ORDER BY Seq;`;
    let tableName = context.Transactions[0].Values.TableName;
    result = five.executeQuery(sql, 0)
    five.log('table: ' + tableName);
    return five.success(result);
}

This is simple, but requires us to first have a field defined on the form for table name, and load that via On Show event using the logic from Jo.

Here is the prior method, using a client-side function called from On Complete:

function ReSequenceList(five, context, result)  {
    
    // get table name (THANKS JO!)
    var myTable = five.getFive().getUserAction(five.getAction(five.actionID()).key()).getForm().getDataSource().dataSourceId();

    // setup parameters
    let myParms = {
        Table: myTable
    };
    
    five.executeFunction('ReSequenceListServer', myParms, null, '', '', function (result) {
        if (result.serverResponse.errorCode === 'ErrErrorOk') {
            five.refreshTable(myTable);
            five.reload();
            return;
        }
        const functionMessage = result.serverResponse.results;
        if (functionMessage !== '') {
            five.showMessage(functionMessage);
        }
    });
    
    return five.success(result);
}

And here is the server-side function:

function ReSequenceListServer(five, context, result)  {
    
    let myTable = context.Table;
    five.log('ReSequenceListServer (' + myTable + ')');
    let sql = `SET @new_seq = 0; UPDATE ${myTable} SET Seq = (@new_seq := @new_seq + 10) ORDER BY Seq;`;
    five.log(sql);
    result = five.executeQuery(sql, 0, context.UserKey)
    return five.success(result);
}

This method uses more code, but doesn’t require adding a field to each form whose table may need to be re-sequenced.

Please advise pros and cons of using the 2 methods.

Here is fdf for sample app. Either way, we need Jo’s method to get the table name. The question is, do we use that method at form show time and require an extra form field, or use her method in a client-side function from On Complete.
testing-20241118-174600490047365.fdf (3.4 MB)

Thanks!!!

UPDATE: I modified the form events to use the client-side function which calls a server-side function, all caused by On Complete event. This did not work. Nothing got re-sequenced. When I removed the On Complete and re-added the simpler ReSequenceReports function to Do Complete, it works again. So we still need to figure out why the table name is in the Transactions array, but can’t be referred to directly in code without giving an error.

Hello,

I just imported your updated testing application (my one that you updated) and it seems to log the table name for me. Within the inspector, did you scroll to the bottom of the log?

This is what displays in my inspector when I create a new record using your code,

Double check to see if it is displayed within the five inspector at all, let me know if any issues.

Thanks,
Riley.

Thanks Riley.

No I did not scroll to the bottom. It appears my inspector is not scrollable. When it gets to the bottom of the visible inspector window, the rest of the text is lost.

I changed my application to do what your testing application does, and it does indeed show the table. Now the re-sequence function can be more generic, working for any form whose table has a Seq field. Unfortunately, it’s a lot of work, because I need to pre-load that field value into a form field at form-show time so it gets into the Transactions collection.

I wish there was a way to do that through the On Complete event, but as I mentioned in my prior comment, that does not work. Any ideas why? Thanks again…

Hello,

Given that On Complete requires you to call a client side function in which calls a server side function, you are unable to update fields/general UI material within the callback of this server side function,

Hope this helps.

Thanks,
Riley.