Hi folks,
My use case is to select and display a random row from a table in a region when page loads. I seem to get all the parts but can't figure out the sequence to make it work. Here's what I have:
Table is HELLO_TBL, three columns: USER, ID, PHRASE:
- USER_A, 1, “Hello World!”
- USER_A, 2, “Bonjour Monde!”
- USER_A, 3, “Ciao Mondo!”
- USER_B, 1, “Hello World!”
- USER_B, 2, “Vannakam Ulakam!”
etc. Each user has 1-many rows and the set of IDs is unique to each user.
On homepage I have a Card region. The intent is to display one of the user's rows in HELLO_TBL randomly. SQL is:
select PHRASE from HELLO_TBL where USER = :APP_USER and ID = :P1_RANDOM
:P1_RANDOM is a page item.
I used JavaScript to generate the random number, vs other approaches.
phraseMax = apex.item("P1_MAX_PHRASE").getValue();
phraseRan = Math.floor(Math.random() * phraseMax) + 1;
apex.item("P1_RANDOM").setValue(phraseRan);
P1_MAX_PHRASE is a page item that selects the max ID from HELLO_TBL for the current user.
select max(ID) from HELLO_TBL where USER = :APP_USER
I can get the max ID, I can get the Random ID, and I can return a row for the user/ID in where clause. The problem I have is getting this sequence. Should flow like this on page load
- get the max ID into :P1_MAX_PHRASE
- execute Javascript, utilizing :P1_MAX_PHRASE to set the value of :P1_RANDOM
- load the Card region via the SQL against HELLO_TBL for the user and random value in :P1_RANDOM.
I can't figure out how to get this sequenced. I tried a Dynamic Action on pageload and while it generates the random value it does so after the region loads. I added a refresh into the DA, which reloads the card but it comes up empty. I tried putting the JS into region initialization javascript and got a TypeError.
I think I have all the parts but getting the random ID passed into the SQL before it executes to load the Card seems to be the problem.
Suggestions?
Chris