I am trying to automate my journal creation process.
I track progress in 10 different areas, but I will use only 1 to explain in detail.
In table CATEGORIES I have different categories I track, LEARNING is one of the categories.
In table RATE I have 10 entries for each category, that correspond to evaluation from 1 to 10.
In table ACTIONS I log time spent on each category.
In table ENTRIES I store monthly entries, one entry contains results in each of 10 categories of that month.
I want to automate the process, that is write a script, that would perform automatically and make a monthly entry.
The sequence would be like this:
1) Go to ACTIONS layout
2) Find actions of the previous month and the corresponding category
3) Set total time as a variable
And here is where I get stuck.
Next I need to be able to "evaluate" using evaluation marks from 1 to 10 from RATE table.
Fragment from RATE field
8 = 16 (hours)
7 = 14 (hours)
6 = 12 (hours)
So if the total time variable is 15, it would return 7 (as 15 are higher than mark 7, but lower than mark 8) and then I could put that into a journal entry.
How can I get correct evaluation marks from RATE field without performing find 10 times for each of 10 marks? Lookup? Or anything else? Please provide detailed explanation.