AnsweredAssumed Answered

Lookup (or alternative) question

Question asked by ultranix on Jan 25, 2019
Latest reply on Jan 26, 2019 by sabau



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.