I created a sample app for you. This is really complicated to explain, but I’ll try my best.
Here’s the link to the app. You should be able to copy it.
Challenges Sheet - This is a basic list of all the challenges.
- Challenge - Sheet Column - The challenge Name
- Description - Sheet Column - Description of the Challenge
- rel-Seminar - Glide Column - This is a relation from the challenge back to itself. The tab will only show one detail record when we use the Detail Layout, but I wanted an inline list Card Layout, so the relation of the currently viewed row will only show one inline list card.
- rel-Completed - Glide Column - This is a relation that links the Challenge to the if-CompletedChallenge column in the Completed Challenges sheet. I’ll explain later, but the relation will only find a match if the signed in user completed a challenge.
- lkup-CompletedDate - This is a lookup column that uses the rel-Completed relation to bring back the completed date for use in filtering which challenge is displayed.
- tmp-CompletedChallengeButtonLink - This is template column with a hashtag used to create a button that does nothing when clicked. This button will be displayed when the challenge is completed for the user so they cannot submit a challenge again.
- if-ComeBackTomorrow - this is an if/then column that will fill with text indicating that the challenge is completed and to come back tomorrow. This will display in the card as a reminder that the challenge has been completed.
Completed Challenges sheet - This will store all challenges that all users have completed.
- Challenge - The challenge that was completed.
- Email - The email of the user that completed the challenge.
- Date/Time - The current Date and Time that the user completed the challenge.
- Confirm - Stores the checkbox that the user needs to check to confirm that they completed the challenge.
- if-CompletedChallenge - This is an if/then column that will write the challenge name to the column only if the email in that row is the signed in user. This is the key that makes the relation and lookup on the Challenges sheet work. That relation will only return something if the signed in user completed a challenge and it was submitted to the Completed Challenges sheet. There is more on how that works here: List Relation "Data"
The app’s main tab is using the Challenges sheet and displaying using the Details Style Layout. This means that the first row that matches the filter and sort order of the sheet will be the first row that’s displayed. This tab will display the record of the first row that either has a mathing lkup-CompletedDate that’s empty, or the first row that has a matching lkup-CompletedDate that is on or after Today. So if a challenge has not completed, it will show that challenge. Once it’s completed, it will still show until after midnight (it’s not 24 hours, but it is the next day). Then the next challenge is shown because the lkup-CompletedDate is empty.
Inside the challenge details is a form button. This is the only way to be able to submit a new record to the Challenges Completed sheet while also automatically including the Challenge name, the user’s email, and the completed date. On the form the user will need to check a Confirm checkbox to allow them to submit the form. Since there isn’t much to show in the form, I added some text to explain to the user how to submit.
Back on the Challenge details, there are two buttons with visibility conditions set. The form button will display if the rel-Completed relation is empty, meaning there isn’t a matching completed challenge found in the Challenges Completed sheet for that user. Once completed, then there is a Challenge Completed button with the Open Link action. This button will only display if the rel-Completed relation is not empty, meaning that a completed challenge was found in the Challenge Completed sheet for that user. The tmp-CompletedChallengeButtonLink column with the hashtag is what’s used for the link to open on the button. This will cause the button to basically do nothing. Since the challenge is completed, there is nothing that the button needs to do.
Best of luck completing your app.