topic Re: Setting Date on Sheet activation in App Development

Setting Date on Sheet activation

Oggy172 — Wed, 04 Jun 2025 10:01:33 GMT

Hi all,

Can't seem to get this to work properly.

I want to preset a field (LoggedDate) as Yesterdays Date. Logged Date is natively in format "DD/MM/YYYY hh:mm"

so I have the below:

Calendar: LOAD date(Date,'YYYYMMDD') AS TodaysDate, date(Date-1,'YYYYMMDD') AS YesterdaysDate FROM [lib://QVD Data:Office_365_Sharepoint - a@b.com.com//Shared Documents/QlikSaaS/Calendar2.qvd] (qvd) where Date = Today(); let vToday = peek('TodaysDate', -1, 'Calendar'); let vYesterday = peek('YesterdaysDate', -1, 'Calendar');

To set the variables....

and this to grab the logfile:

[ChangeLog]: LOAD [Entry_No], [Date_and_Time], date(floor([Date_and_Time]),'YYYYMMDD') as LoggedDate Resident [Rest Connection Master Table]

I then have an action to "Set Field Values" for LoggedDate to =$(vYesterday)

but it doesnt do anything, if I surround the two parts of the load script in NUM() it works, so it seems its format related, but try as I may, I cannot get it working

Re: Setting Date on Sheet activation

-SW- — Fri, 06 Jun 2025 09:52:43 GMT

Can you remove the = from =$(vYesterday)

and try again?

Re: Setting Date on Sheet activation

Oggy172 — Fri, 06 Jun 2025 09:59:10 GMT

If you mean in the set field value, then it forces it when I close the expression editor

Re: Setting Date on Sheet activation

-SW- — Fri, 06 Jun 2025 10:00:39 GMT

As in, remove that character "=" after the expression editor in that screenshot as you see it.

Re: Setting Date on Sheet activation

Oggy172 — Fri, 06 Jun 2025 10:18:10 GMT

Ah, didn't think of that, sadly it's the same....

No filter applied...

Would it be beneficial to upload an anonymised sample?

Re: Setting Date on Sheet activation

-SW- — Fri, 06 Jun 2025 10:21:02 GMT

Go for it, I can have a quick look.

Re: Setting Date on Sheet activation

Oggy172 — Fri, 06 Jun 2025 10:50:37 GMT

This is obviously massively cut down and pointed at inline tables, as my actual copy takes the data from our Business Central 365 data

Hopefully it behaves the same!

Re: Setting Date on Sheet activation

Adam_Romanowski — Fri, 06 Jun 2025 10:53:03 GMT

Maybe instead using "Select values in field" try "Select values matching search criteria". It will probably work.

Re: Setting Date on Sheet activation

Oggy172 — Fri, 06 Jun 2025 11:02:38 GMT

Hi Adam,

Yes this worked, though I am keen to know why the first solution didn't - as that seems the obvious pick (and worked when I converted the date to it's number/serial)

Thanks!!

Re: Setting Date on Sheet activation

marcus_sommer — Fri, 06 Jun 2025 11:41:26 GMT

I suggest to avoid always to match/calculate anything against formatted values else using only pure numeric values.

Beside this I would tend to skip the variable stuff and using an appropriate flag-field from the calendar, for example:

DateField - today() as Flag

and then 0 means today and -1 relates to yesterday.