Automation issues - no results from queries

[andyburgessmd]

Checklist

• I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method: docker
  • Budibase Version: 2.31.6
  • App Version: 2.31.6 - though just updated to this in hope it would fix issues which were present before

Describe the bug
This is tricky to describe as it is affecting automations in different ways.
Example 1
I have an automation which runs on cron and should send emails to users if there is a internal db row with them as an assignee and sent != true. It stopped sending the emails.
This automation has 5 steps

But automation logs for this show that only the trigger cron step

Example 2
A different automation in another project is triggered by a row creation, it them does a query against another table (counter) using a date value from the trigger row and if there is already a row with that data it increments the counter.
But the query does not now return any rows so the condition/if then fails. I have checked the data and there is a row that it should match but it does not return it.

I have been on leave so unsure when this all started, or what version.

Have there been any updates/changes that could have broken this as nothing has changed our side, except where trying to fix it.

[linear]

BUDI-8628 Automation issues - no results from queries

[PClmnt]

@andyburgessmd Hi Andy, what are steps 3, 4 and 5 in your first example? Would you mind sharing them?

[andyburgessmd]

@andyburgessmd Hi Andy, what are steps 3, 4 and 5 in your first example? Would you mind sharing them?

[PClmnt]

@andyburgessmd There is a fix about to be released for this in the coming minutes. I noticed it yesterday but we hadn't had a chance to deploy. Really sorry about this! When you update it should be fixed

[andyburgessmd]

@andyburgessmd There is a fix about to be released for this in the coming minutes. I noticed it yesterday but we hadn't had a chance to deploy. Really sorry about this! When you update it should be fixed

Thanks. Weirdly it just seems to have run the automation without me updating anything my side. Is that expected?

[PClmnt]

@andyburgessmd There is a fix about to be released for this in the coming minutes. I noticed it yesterday but we hadn't had a chance to deploy. Really sorry about this! When you update it should be fixed

Thanks. Weirdly it just seems to have run the automation without me updating anything my side. Is that expected?

I'm not so sure how that has happened! Keep an eye on the automation and if there are any more issues please tag me here and I will investigate further. Thanks again for your patience with this.

[andyburgessmd]

@PClmnt the automation in example 1 is running now after the update - however the automation logs are still only showing the trigger for that automation

For example 2 this is still failing. The query is still returning 0 rows.
Here is the automation step.

And here is the input shown in that step from the automation history
{ "tableId": "ta_7bcbb22918a84a8bb53ff0b0ee321b48", "filters": { "string": {}, "fuzzy": {}, "range": {}, "equal": { "1:datenum": "{{ date trigger.row.date "YYYYMMDD" }}" }, "notEqual": {}, "empty": {}, "notEmpty": {}, "contains": {}, "notContains": {}, "oneOf": {}, "containsAny": {}, "onEmptyFilter": "none" }, "filters-def": [ { "id": "CfiRidhyZ", "field": "1:datenum", "operator": "equal", "value": "{{ date trigger.row.date "YYYYMMDD" }}", "valueType": "Binding", "type": "number" }, { "onEmptyFilter": "none" } ], "onEmptyFilter": "none", "sortColumn": "datenum", "sortOrder": "ascending" }

[PClmnt]

@andyburgessmd Investigating the second one now, as to the first one I can't seem to reproduce? Did you update and publish your app again? Just trying to rule a few things out

[andyburgessmd]

@PClmnt Yes, app was updated to 2.31.7 and published.

[PClmnt]

@andyburgessmd Would you be able to provide an export of your app so I can investigate further? You can send it to[email protected]

[andyburgessmd]

@PClmnt sent the files

[andyburgessmd]

@PClmnt FYI removed the delay step in the automation and it now shows correctly in the automation logs (for issue 1)

[PClmnt]

@andyburgessmd Thanks Andy, I'm working on a fix for the second issue at the moment.

[PClmnt]

@andyburgessmd can you try the following JS in the query rows filter, attached an image to illustrate. In your design section I noticed that time was unselected from the date picker settings causing BB to send back a string like YYYY-DD-MM instead of the full ISO date. You could theoretically just pass this through without any handlebars now, just {{trigger.row.date}}. However we don't support the removal of the time from the date string in the automation test section so this JS just covers both cases. We'll update that soon to support it.

    
const year = date.getUTCFullYear();
const month = String(date.getUTCMonth() + 1).padStart(2, '0'); 
const day = String(date.getUTCDate()).padStart(2, '0');
    
return `${year}${month}${day}`;

In here:

[andyburgessmd]

@PClmnt thanks. This has been working for at least a year as it is, has something changed which has caused it to break of all a sudden

[PClmnt]

@PClmnt thanks. This has been working for at least a year as it is, has something changed which has caused it to break of all a sudden

That's the bit I'm still a little confused about, I was trying to track down any changes we made and there was some stuff in that area a couple of months but nothing in recent weeks. Potentially the app only got published again recently?

[andyburgessmd]

const year = date.getUTCFullYear();
const month = String(date.getUTCMonth() + 1).padStart(2, '0');
const day = String(date.getUTCDate()).padStart(2, '0');

return ${year}${month}${day};

I have been trying this but can't get it to work. I have tried it in a backend log step on its own to see what it returns but I can't get it to return anything except an 'Error while executing JS' response.

I have tried just returning the year but that returns the same.

[PClmnt]

    const date = new Date($("trigger.row.date"));
    
    const year = date.getUTCFullYear();
    const month = String(date.getUTCMonth() + 1).padStart(2, '0'); 
    const day = String(date.getUTCDate()).padStart(2, '0');
    
    return `${year}${month}${day}`;

@andyburgessmd Sorry, bad paste into here! That should be correct now. I just tested that and the 'addcounter' automation ran successfully after i submitted a new desk booking

[andyburgessmd]

@PClmnt thanks I have that working now. I did add to add one extra bit to return as an int instead of a string to get the query part to work but it is now running. Thanks for your help