set sql-field with date-format

MadMax76

when clicking a checkbox I want one field to be set to '1', that works perfect. Additionally I need to keep track of the time when this happened. I have this code for this:

        $('#liste_freigaben').on('change', 'input.editor-active', function () {
            editor
                    .edit($(this).closest('tr'), false)
                    .set({
                        'V_Rechnungern_mit_freig.freig_erledigt': $(this).prop('checked') ? 1 : 0,
                        'V_Rechnungern_mit_freig.freig_angefordert_wann': function () { return new Date(); }
                            })
                    .submit();
        });

As mentioned the first one works, the second doesnt. Here the json-response:

        "query": "UPDATE  [freigabe] SET  [freig_erledigt] = :freig_erledigt, [freig_angefordert_wann] = :freig_angefordert_wann WHERE [ID_freigabe] = :where_0 ",
        "bindings": [
            {
                "name": ":freig_erledigt",
                "value": 1,
                "type": null
            },
            {
                "name": ":freig_angefordert_wann",
                "value": null,
                "type": null
            },
            {
                "name": ":where_0",
                "value": "463",
                "type": null
            }
        ]
    }
}

If I put "2021/03/03" instead of "function () { return new Date(); ", this does get put into the correct "value"-field above, but formatted as text, therefore the sql-server ignores this.

In the server-side I have set this:

        Field::inst( 'V_Rechnungern_mit_freig.freig_angefordert_wann' )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ))
            ->setFormatter( Format::dateFormatToSql('Y-m-d' )),

What could cause the problem?

allan

set() doesn’t accept (and evaluate) functions as the value. You don’t need a function here anyway - try:

$('#liste_freigaben').on('change', 'input.editor-active', function () {
    let now = new Date();

    editor
            .edit($(this).closest('tr'), false)
            .set({
                'V_Rechnungern_mit_freig.freig_erledigt': $(this).prop('checked') ? 1 : 0,
                'V_Rechnungern_mit_freig.freig_angefordert_wann': now.toISOString().split('T')[0] }
                    })
            .submit();
});

Regards,
Allan

edited Remove smart quotes

MadMax76

Hi Allan,

the ‘T’ is marked as error in php-storm, if I change it to 'T' then a red mark comes up behind [0] }

If I just leave it as you suggest, the editor doesnt fire at all for the checkbox.

Thanks
Max

allan

Oo yes - I had smart quotes in there from somewhere - sorry about that! It should indeed just be 'T'.

I guess PHPStorm doesn't like the [0] because we should check for the length of the array (although this is one of the cases were we can say for sure that it will have two components, since that is how toISOString works. Let's try:

$('#liste_freigaben').on('change', 'input.editor-active', function () {
    let now = new Date().toISOString().split('T');
    
    editor
            .edit($(this).closest('tr'), false)
            .set({
                'V_Rechnungern_mit_freig.freig_erledigt': $(this).prop('checked') ? 1 : 0,
                'V_Rechnungern_mit_freig.freig_angefordert_wann': now.length ? now[0] : '' }
                    })
            .submit();
});

Allan

MadMax76

Hi Allan,

thanks once more - now you have a } to much :-)

But also after removing that the data send is

{
    "1": {
        "name": ":freig_angefordert_wann",
        "value": "2021-02-23",
        "type": null
    }
}

this is interpreted by sql as text, and therefore ignored.

I have tried this with and without

            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ))
            ->setFormatter( Format::dateFormatToSql('Y-m-d' )),

makes no difference.

completely other points:
- how can I start a thread as "Priority"?
- - I did never get emails about updates to threads, although I should according to my settings
- - I cant comment the one thread I have in priority. For other people I would like to update the solution there.

Max

allan

this is interpreted by sql as text, and therefore ignored.

What database server are you using? SQL Server, MySQL, Postgres? I'm going to guess MySQL based on the note about the value being ignored if it is in error! However, we use MySQL extensively with Editor for this sort of thing and it will correctly see that ISO8601 string as a date. In fact, all of the database servers we support will do.

Are there any error messages at all from the database server? Can you show me the full query that is being executed?

Meta stuff

how can I start a thread as "Priority"?

When you click the "Ask a question" button you should get a page like this:

Click the "Category" dropdown and select the category you want. As I say though, it doesn't make much difference.

I did never get emails about updates to threads, although I should according to my settings

Have you checked your spam folder?

I cant comment the one thread I have in priority. For other people I would like to update the solution there.

I've moved it to "General" - could you try it now? Something odd is going on with the forum permissions!

Allan

MadMax76

ad threath - works now, my error -THANKS!

now I see the field for category, I must have been blind up to now.

And now I also get mails!

Max