Sort Items in List based on Field Value

[RMittelman]

My SetupReports form is based on my Reports table. The form basically works, and I can see the various reports in the list on left-hand side of form.

I am displaying the report name and the Seq field in the list. Everything works ok, except I want to sort the list by the Seq field rather than by Report Name. I can find no place to configure the sort order of the list.

Does the list sort automatically based on the first field in the list? I’d rather have the Seq field AFTER the report name field but still sort by Seq. If this is not possible, I can move the Seq field up above the report name in the Fields tab. Please advise.

To try and make this work myself, I created a query called GetReports, and included every field in the table but set the query to order by Seq. This works in the query design when I run it. I made the query LIVE. Then changed the data source of the form from Reports to GetReports query. The form still displays properly, and the list IS sorted properly, but when I click on a field in the form, nothing happens, as if the form is now read-only.

Please advise the proper way to sort the list, I can’t find that in the 2.7 docs. I can only find how to filter the list. Also, if possible, perhaps answer why the form is now non-editable if I use a query as its data source.

UPDATE: By moving the Seq field above the Report Name field in the fields list of the General page, it does indeed now sort the records in the proper order by Seq. However, the Seq column in the list is VERY wide, as wide as the Report Name field. This is a quite unpleasant visual effect. I can’t figure out how to change the width. At run-time, I can change it, but if I leave the form and come back it is wide again. I tried to change to 5, then to 1 in the display tab, but nothing helped. That only caused the display width of the field on the form to change instead of the list.

So to Recap:
1: Is there a simple way to sort the list by NOT the first field in the list?
2: If I must sort by the first field in the list, how do I change the column width in the list?
3: Is there a statement I can put at the end of my function to cause the list to refresh itself without having to exit the form and reopen it again?

Thanks…

[RMittelman]

Another Update:

After some research, I designed the following query to reorder my records and re-sequence them:

SET @new_seq = 0; UPDATE Reports SET Seq = (@new_seq := @new_seq + 10) ORDER BY Seq;

So if I have 2 reports, with Seq values of 10 and 20, If I want to re-order them, I pick the report with Seq=10, then change it to 21 and save the record. Next time I open the form, it shows them in the proper order with Seq values of 10 and 21.

The above ReSequence SQL was supposed to re-number them so the Seq values would then be 10 and 20, but with the desired order maintained (the old 21 being changed to 20).

This doesn’t seem to work when included in a server-side function. The sequence numbers remain at 10 and 21.

However, if I open a new query in design mode, then place this SQL in the editor:

SET @new_seq = 0; UPDATE Reports SET Seq = (@new_seq := @new_seq + 10) ORDER BY Seq;
-- Select * from Reports

When I run the query, then edit it so the first line is commented and the second line is not commented, then run again, it shows the re-sequencing works.

So why does this work in the query design window, but not properly work at run-time when the function is executed?

Here is fdf if necessary:
BrandeisConejo-20241025-224158101177796.fdf (5.9 MB)

Thanks…

[RileyAnderssen]

Hello,

Unfortunately you are unable to order the list in which appears on the left side of the form. You are also unable to customise the display of this list regarding the width of columns.

You are able to order a particular column by hovering over the header of a column and pressing the arrow button,

Hope this helps,

Thanks,
Riley.

[RMittelman]

Thanks for the reply Riley. However, I’m afraid I confused the issue by combining 2 different issues in the same post.

If I put my Seq field higher in the field list so it appears to the left of the name, it does indeed seem to sort by the Seq. Even though the screen looks funny with the Seq being very wide, it is still workable.

Separate from the list display order there is another issue:
For easier re-ordering of the items in the list, I want to change the Seq number field to a different number, causing the list to re-order. So if I have the following list:

I would edit the first record (10 Roster) and change the Seq to 21, then save the record. On saving, the ReSequenceList function should cause the now current list of 21 Roster and 20 Rosterr to be re-sequenced to 10 Rosterr and 20 Roster, in that order, using the following SQL:
SET @new_seq = 0; UPDATE Reports SET Seq = (@new_seq := @new_seq + 10) ORDER BY Seq;
Once this is done, I expect the reports to reappear properly, but instead get this:

If I leave the SetupReports form, then come back, I see this:

Showing that A) the on-form list is indeed properly sorting, and B) the function that was supposed to re-sequence the records from 20,21 to 10,20 did NOT work.

Funny thing, if I navigate back to the Five design-time tab, open a new query and execute it, the results are immediately live if I navigate back to the run-time tab.

So I ran the above SQL code in a new query window in design-time tab, then went back to run-time tab, navigated to another form, then back to the SetupReports form, and now I see this:

This is the expected end result of changing a Seq number field of a record. This same logic has been working for years in my Access applications.

So here are the remaining issues for this thread, and they are very important:

1:
Why is the ReSequenceList client-side function, which calls the ReSequenceListServer server-side function, which runs the above SQL code not work to re-sequence the records, but if I run the same SQL code in a query window it does work? Am I calling the functions from the wrong event?

2:
How can I get the list to re-load without having to leave the form and come back to it? The list IS refreshed to a point, because it went from 10 Roster, 20 Rosterr to 21 Roster, 20 Rosterr as you can see by prior screen shots. I had to leave the form and come back to see the list displayed in the proper order.

Of the 2 issues, #1 is much more important. I can live with the list not being able to instantly requery itself. But there must be a way to get the update query to actually run from the live run-time web page.

Thanks so much for looking at this issue!

[Jo-Anne]

Hi Ron,

My apologies for this post being overlooked and you receiving a late response! Please refer to the following for the answers to your questions.

1. Is there a simple way to sort the list by NOT the first field in the list?
By default we sort the list by the first field included in the list. You can click the sort button in the header, however it won’t hold the value.

The image below shows the list before clicking the Sort button.

The image below shows the list after clicking the Sort button for a field that is not the first in the list.

I would go with your suggestion to put the Seq field before the Reports field.

Clever idea to use a live query in Five to try and make this work for you, however, the reason your form turns to read-only though is because the query results are read-only.

There is no documentation in the 2.7 version on how to reorder the list as we handle it by default with the first field included in the list, then by the second and the third etc.

2. If I must sort by the first field in the list, how do I change the column width in the list?
You can change the column width by using the slider in the header, however, this width won’t hold when leaving. This feature has been reviewed in our weekly product review meeting and is in the pipeline as a future feature.

Please note, we do handle this in data views by setting the widths on the Display page. When you use the Display page on a form it only handles the fields in the form, not the fields in the list.

3. Is there a statement I can put at the end of my function to cause the list to refresh itself without having to exit the form and reopen it again?
The list of records on the left does not honour live queries or our refreshTable() function. So at this stage you do need to refresh the view to see the new order. I will bring up your feedback in our next product review meeting.

Please let me know if I haven’t answered something and once again my apologies for the late reply.

Kind regards,
Jo

[RMittelman]

Thanks for replying. I am not as interested in how you sort the list once it’s displayed as much as having it displayed in the proper order. For now I’m willing to wait on that. I like your suggestion for enhancing the system to honor refresh request and/or being able to set list widths by parameter.

However, the most important part of my post wasn’t addressed. Please see #2 on my last reply. Why will my ResequenceList function work if I call it from the query design window but not work when called from an event at run-time? Thanks…

[Jo-Anne]

Hi Ron,

Five.executeQuery was being provided a parameter, however, the SQL did not reference this and MySQL had an issue with the supplied query.

You can see from the screen shot below, if we log the result from executeQuery and run with inspector, you can see the error (a bit obscure, but thats MySQL for you).

However, if you fix this, it probably does not work as you maybe expecting, as OnComplete runs before the new data is entered into the database, and therefore the sequence update only affects previous records, unless this is what you are wanting.

Suggest another idea that might be what you want, create a Do Before Insert trigger for the Reports table, and you can insert new reports, and also the new sequence will appear in the list as well, which looks nicer I think.

So just to clarify, use the function provided on the Do Before Insert event on the Reports table. From this you maybe able to expand on what you want to do.

function BeforeNewReport(five, context, result)  {
    const queryResult = five.executeQuery('SELECT MAX(Seq) AS Seq from Reports', 1);
    const maxSeq = parseInt(queryResult.records[0].Seq);
    context.new.Seq = maxSeq + 10;
    return five.success(result);
}

Kind regards,
Jo

[RMittelman]

Thanks so much for addressing my issue Jo. I did not realize I put that parameter in the execute statement. However, I fear you are right if the event runs before the record is actually inserted.

I appreciate your alternate approach, but it won’t work for me because it only seems to address what happens when you add a new record. For that purpose, it will work.

My use-case includes both adding a new record (that’s why the Seq field is designed to default to 999 on a new record) AND re-sequencing. Imagine a Reports table with Seq and Name like the following:
10: First Report
20: Second Report
30: Third Report

In case the user wants to see
Second Report
First Report
Third Report

in that order (please ignore the stupid report names), they would display First Report and change its Seq from 10 to 21, then save. Now they are in the proper order, and next time the SetupReports form is loaded, you will see the right order.

The ResequenceList function would then re-number the records to 10, 20 & 30 and everything would look perfect.

Is there no event that occurs AFTER the new record is inserted or an existing record is changed and saved? That’s what I would need for the 2 different use-cases. If there is another way to re-sequence existing records, then I could use your suggestion for the new records and some other way to re-sequence the existing records.

Thanks again…

[Jo-Anne]

Hi Ron,

Keep the BeforeNewReport function on the Do Before Insert event on the Reports table as this will handle new records.

When a form is being updated add the following function on the Do Complete event for the Reports form:

function AfterReportUpdate(five, context, result)  {
    let sql = `SET @new_seq = 0; UPDATE Reports SET Seq = (@new_seq := @new_seq + 10) ORDER BY Seq;`;
    result = five.executeQuery(sql, 0)
    return five.success(result);
}

This will handle updating a record. When I tested it, it does not update the list though until the view is refreshed for the same reasons as I said in the previous post. I have taken note of this to bring up in our next product review meeting.

Kind regards,
Jo

[RMittelman]

Thanks for clarification Jo. Of course, the answers just result in new questions…

I was hoping there was a way for the function to know what the table is, so I could use it for any table with a Seq field. I had another topic on the forum regarding this, but nobody was able to tell me how the current function could “know” which table to update. Is this possible?

If not possible to do this, can I set a variable when opening any form, which points to the underlying table name? And will a server-side DO… event be aware of variables that have been set?

Alternatively, can I call a function from a do after update event but give the function an argument from the event? For example: Do After Update: ReSeqTable(“Reports”). If yes, can you show me how to code the function to include the argument sent?

If there is a way to do this, then why not use a single function called from Do After Update to handle both cases of adding or updating a record? If there is NOT a way to know the table name in the function, I could use a different similar function on each table that has a Seq field, right? I’d prefer a single function but if necessary multiple functions will work.

The pop-up help on the table events page doesn’t quite answer this, but do I need the same function to be called after insert and after update, or does after update event also get called after an insert?

If we can make this work, then I can simply leave the default value of Seq field as 999, and the function should update it correctly, then I wouldn’t need 2 different functions.

I like your idea of using Do Before Insert to make the new Seq field show the corrected value immediately, but then it seems confusing because it works one way when adding a record and a different way when updating one.

Can you please answer the bold questions for me, then I can proceed with this feature? Thanks, I really appreciate the help!

[RMittelman]

Update:

In the Tables screen, I opened up the Reports table, and on the Events tab I put “ReSequenceReports” in the Do After Update event.
I added the following function:

function ReSequenceReports(five, context, result)  {
    let sql = `SET @new_seq = 0; UPDATE Reports SET Seq = (@new_seq := @new_seq + 10) ORDER BY Seq;`;
    result = five.executeQuery(sql, 0)
    return five.success(result);
}

Then I ran the application, opened the SetupReports form, and saw the following in the list:
10 Rosterr
20 Roster

Then I loaded the Rosterr item and changed its Seq from 10 to 22 in order to change the list order. I saved the record, then exited the form, opened anothe form, then came back to setup reports, and see the following in the list:
20 Roster
22 Rosterr

So the change took place, but the re-sequencing did not work.

[RMittelman]

Another Update:

Still, the sql you provided works in the query window if I run it, but won’t work when the function is called by an event on the run-time tab of browser.

Another curiousity: I added the Seq field to the field list on the GenerateReports form, then moved it higher in the field list, then in the General tab of the form I told it to use Seq as the order field. Now it shows in the list, but not sorted by Seq. I looked all over the SetupReports form to see if I missed anything. Both SetupReports and GenerateReports appear to be same in their definition, but SetupReports shows items in Seq number order but GenerateReports does not. Do you know what I may have missed?
Thanks…

[Jo-Anne]

Hi Ron,
For knowing what the table name is that is attached to a form, as a work around for now, you can create this function and attach it to the OnShow event of the form.

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

Then create a field called TableName, and don’t assign it to a field in the database. You do this by not selecting a field in the Field field on the Form Fields form. We are basically going to use this as a temporary variable.

Now hide this field.

Show If | Five | Low-Code For Real Developers

Then in the Do Complete event, you can access it from the transactions being passed in as TableName.

Forms | Five | Low-Code For Real Developers

Our online documentation will be updated shortly with an example of the Do Complete event, but for now here is the example that is used in the online help when updated of a Do Complete function.

function DoComplete(five, context, result)  {
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    // When DoComplete is called, all data has been updated in the database, however, it has not been committed
    // yet, so as a last resort, if this function returns an error, all the data will be rolled back from the database
    // Any 'before' table events that have been executed will roll back as well
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    const formRecords = context.Transactions; // An array of FormRecord objects
    for (let i = 0; i < formRecords.length; i++) {
        const formRecord = formRecords[i];

        ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        // formRecord.ActionID <-- is available to know which form is being created/updated/deleted
        // formRecord.Type <-- contains the actual action being performed. 'CREATE', 'UPDATE', 'DELETE'
        // formRecord.Key <-- is the primary key of the record being created, updated, or deleted
        ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        if (formRecord.Type === 'CREATE') {
            ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
            // checking to ensure the FieldFromForm does not contain integer values to demonstrate an error
            ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
            if (/\d/.test(formRecord.Values['FieldFromForm'])) {
                return five.createError('Data for Field From Form cannot include digits');
            }

            const createResult = five.executeQuery('UPDATE Demonstration SET FieldFromCode=? WHERE DemonstrationKey=?', 0, 'Data saved from this function', formRecord.Key);
            if (createResult.isOk() === false) {
                ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                // Since we are returning an error, the database will be rolled back
                ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                return five.createError(createResult, 'Failed to update record');
            }
        }
    }

    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    // Returning a success result here will automatically commit the data into the database.
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    return five.success(result);
}

At the moment setVariable does not support DoComplete, however, the above solution should get you around it.

If the above solution works, it should hopefully cover most of what you are trying to achieve and solve all your questions.

Hope this helps.

Kind reagrds,
Jo

[Jo-Anne]

Hi Ron,

In regards, to your fields not being sorted by the Seq field, I would need your FDF to see how you set it up to try and work out what you have missed.

Kind regards,
Jo

[Jo-Anne]

Hi Ron,

For the re-sequencing not working on the Do After Update event, I would also need to your app to check your settings as it works on our end.

Kind regards,
Jo

[RMittelman]

Thanks for the help Jo.
BrandeisConejo-20241114-044301291059211.fdf (6.0 MB)

I didn’t quite understand the DoComplete function documentation you provided. Didn’t you say the transaction was not completed until after the DoComplete was finished? Your example seems to be updating something, but it is unclear.

Are you saying now that the function I create and call from the Do Complete event WILL work (at least on your end)? I’m confused. Is there any magic gained by calling the function “DoComplete”? Mine is called “ResequenceReports”. It is hard-coded to refer to the Reports table, but with the work-around you showed me, I may be able to make it more generic. Did I understand you correctly that if I use a form field, even a server-side event/function will be aware of these fields? If so, I can definitely leverage that in other forms.

Regarding the list order, I tried everything I could think of. I examined the SetupReports form, which is showing the list in the expected order. As mentioned previously, I thought I found everything to change on the GenerateReports form, but I may have missed something.

Once we resolve the re-sequencing and list order issues, that will be a lot of progress. Thanks again!

[RileyAnderssen]

Hello,

I am currently unable to obtain the FDF Jo was using, but I may be able to help with part of your enquiry.

testing-20241115-000215981049212.fdf (3.4 MB)

Attached is a simple testing FDF I used which displays how to get the form field values when using a DoComplete event.

Run the application using Five’s Inspector, then go to TableOneMenu, add a new record, save it. Now open the inspector and you will see the values entered into the form within the inspector. From this, you are also able to acquire the key of the newly create record as well as the current ActionID.

The function which does this is called ‘GetFieldValues’ and is attached to the DoComplete event of this form.

Hope this helps,

Thanks,
Riley.

[RMittelman]

Thanks Riley.

First thing I did was to examine your function. Can you explain the statements?

    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);

Are you saying that using five.field.NumberOne will not work in the server-side function, but the Transactions object will contain necessary values? Is this in the documentation somewhere? If I understand you, then I would no longer need a client-side function which calls a server-side function after loading the context object?

Thanks…

[RMittelman]

I notice that if I put the Seq field higher in the field list, when I run the application the list is sorted by Seq in the SetupReports form (although not yet on the GenerateReports form). But a curiousity: Now that Seq is higher in the field list, the form header shows 20 instead of Generate Reports or Setup Reports. Is there any way to pick what field value appears in the form header without messing with the order the records show in the list? Alternatively, is there a way for the header area to NOT show the field value at all, because it’s right there on the general page below the header? I can’t make the header not show, because there are buttons there which don’t show if the header section Show If is false. Thanks…

UPDATE: For some reason, the list order is now correct on the GenerateReports form, and agrees with the order in the SetupReports form. I was playing with the visibility of different sections, so I’m not sure what I did to make it work. I still hope you can explain why it did not work up to now.

[RileyAnderssen]

Hello,

The DoComplete will execute after all data has been updated in the database, however not committed, this is the last chance to reverse all changes, so if the DoComplete event returns an error, all changes will be rolled back. The Transactions array contains all records being created/updated as per the example, where as when dealing with five.field.X in the front end, which directly targets the current form you are working with.

Note: the DoComplete does not allow you to edit the values of any of the FormRecords being updated.

Summary: When using five.field in an On event, this is the current form you are working with. The DoComplete is in the process of writing data to the database, and is executed in that context, and the Transactions array contains many forms being completed at once ie. Any form and all the sub forms simultaneously.

As Jo said previously, there is documentation being added to address the DoComplete event.

Hope this helps,
Thanks,
Riley.