Recursive loop decrementing a date variable

I am dealing with a database that is only accessible via a web GUI. In the query building menu, you can select a date or a date range and request all of the listings from that period. You can also select all of a query's results and export to CSV.

The catch is that you can only export query results that are under a certain number of rows. The largest query period that always falls within this constraint is two days.

I have built a KM macro that will, for dates going forward, periodically run the query for the most recent dates and export the query results.

I now want to get the historical data. So basically, I want a script that loops through all of the actions but, each time it loops, it decrements the date by one day.

What is the best way to do this?

If you were to use a KM Execute Script action in some part of your macro, a first draft of an enumFromThenToDate function in AppleScript might look something like this:

property pUnix : missing value

-- enumFromThenToDate:: Date -> Date -> Date -> [Date]
on enumFromThenToDate(dte, dteNext, dteFinal)
    if missing value is pUnix then set pUnix to unixEpoch()
    set ue to pUnix
    set xs to {}
    set interval to (dteNext - dte) / days
    repeat with i from (dte - ue) / days to (dteFinal - ue) / days by interval
        set end of xs to unixDay(i)
    end repeat
    return xs
end enumFromThenToDate

on run
    -- e.g. starting with the current date
    tell (current date)
        set its time to 0
        set dteStart to it
    end tell
    -- and working back to 100 days ago, in one day steps
    tell (current date)
        set its time to 0
        set dteEnd to (it - 100 * days)
    end tell
    -- Today, then yesterday ... and onwards till 100 days ago
    set xs to enumFromThenToDate(dteStart, dteStart - days, dteEnd)
end run

-- GENERIC --------------------------------------------------

on unixEpoch()
    tell (current date)
        set {its year, its day, its time} to {1970, 1, 0}
        set its month to 1 -- set after day for fear of Feb :-)
        return (it + (my (time to GMT)))
    end tell
end unixEpoch

on unixDay(n)
    if missing value is pUnix then set pUnix to unixEpoch()
    pUnix + (days * n)
end unixDay

1 Like

Thanks for this.

I have a question: Why wouldn't I use the "Execute an AppleScript" action?

Keyboard Maestro often enables more than one route to the same result.

Execute script actions are a natural choice if you happen to be familiar with scripting, but you could probably also get there in more than one way with the built-in action blocks.

So if I'm understanding the logic correctly, will my KM script go through its sequence, hit the AppleScript, which will return -- for example -- November 12, 2018? Then after it executes, the next time I run the KM script, the Apple Script will return November 11, 2018 and so on.

Or will the AppleScript iterate to completion before the KM script has completed one run cycle?

In the draft you have there, it batch-generates a full list of dates.

If that's not what you need, it should be possible to use a 'generator' pattern and applescript property persistence to get it to increment or decrement on each call, though a KM variable might be a more natural way to store the current 'state'.

I think you may need to give us a slightly clearer picture, ideally with a concrete example to test with.