Visibility of a Component based on a different sheet?

Feature Requests
1

Is it possible to set a condition for visibility on a different sheet from where the component is placed? To give a little bit of context my app is an on-demand market place for DIY’ers to get advice or hire someone at short notice. I’ve made good use of component visibility throughout and I’m really proud of what I’ve achieved so far

I’m looking for a solution where once a tradesperson (contractor) has accepted a job they are barred from accepting others until their current job is completed. Using an Uber for example, a driver cannot accept all requests around them as riders would be waiting a very long time and there’d be none left for other drivers.

On a high level view, the spreadsheets handles new requests, job allocation and completion of the requests. Glide handles the form entries and strategically placed visible and invisible components make up the app’s workflow.

for instance

  1. A DIY’er submits a request -> form component
  2. The DIY’er prompted to make payment
  3. contractors can view a feed on unallocated jobs and accept/reject a request with a form
  4. title component’s title and details field are dynamic and communicate when their request is accepted
  5. DIY’er’s request is no longer visible to other tradespeople once accepted
  6. tradesperson will get a clickable link for WhatsApp message with the DIY’ers number, but only after DIY’er has paid.
  7. if after 30 minutes the DIY’er hasn’t been contacted, they will get a clickable link for a WhatApp message to notify me of the delay.
  8. the required service is carried out
  9. tradesperson marks job as complete using a different form

The form responses from the DIY’ers are fed into googlesheets and are further extended on Glides with several relation, lookup, template and if-then-that columns to manage the workflow mentioned above.

There is a clock in / clock out sheet and associated form for the contractors to let the app know who’s online and when. On another sheet, a query pulls in data from a sheet where the tradespeople Accept jobs allowing me to handle job rejections and ensure they don’t feed onto the main job requests sheet.
These two sheets allow me to determine which tradespeople are available, busy or offline on the main tradespeople master sheet.

All unallocated jobs are shown on page in the app as an inline list. As per point 5, a job will disappear once accepted. I tried to reference the status (available, busy, offline) as lookup column in the job requests page but this didn’t work because the records are for the specific job not the tradesperson themselves. Also I think the status would populate until after the job had been accepted anyway (someone correct me if I am wrong).

In my current approach there doesn’t seem to be a way where i can set the visibility of new job request based on the condition of a cell in a different sheet (tradespeople master sheet)

Forgive me if I’m not making any sense and if this has already been asked and answered. Has anyone attempted an uber clone in Glide, does anyone have a suggestions on how to prevent a one tradesperson from gobbling up all the available jobs requests?

2

In Step 1, are you creating a RequestID value by passing a special value -> unique ID?

3

yes, I’m wasn’t 100% sure how to apply them but I made sure that all my forms pass a unique ID value in case they’d come in handy later on

4

Ok. I’m actually working through similar logic in one of my apps. Let me solidify and I’ll get back to you.

1 Like
5

Here’s what I’ve done. Hope it helps you with your app. It was tricky. I wanted a complete inline list of clients to be visible if they HAD NOT been selected by the host. Once selected, I wanted ONLY the selected client to be seen. There was no way for me to set a filter for if it’s empty OR if it’s full, so I had to create two identical components that were filtered differently and had different visibility settings. The result is smoke-and-mirrors-seamless:

6

Hi Hemmings,
I am creating a similar Uber clone app for online tutoring. In the app, there are students who submit tutoring requests, and tutors who accept them. So, I have a similar issue of not allowing tutors to take requests once they have taken a request till the time they complete the request. And, I encountered the same problem you have mentioned. This is how I have solved it.

I have a Tutors sheet, which has details of each tutor, one tutor per row. I have the Tutoring Requests sheet where each student request for tutoring appears as a row. In each row for each tutoring request, there is a Session Completed column, which is true, after the tutor has completed the tutoring session. Each tutoring request row also has an email column which shows the email of the tutor who has accepted the request.

As you mentioned, the visibility filter can only be used from the Tutors sheet, and in the Tutors sheet, I have a column named Currently Not Occupied and a column named Available for each tutor row. If Available is true, it means tutor is available now, and if Currently Not Occupied is True, that means the tutor is not serving any other request and free for new requests. The problem was how to detect from the Tutoring Requests sheet, if the tutor is currently free or not free. Since this sheet will have many requests done by all tutors, first I had to filter only those requests for one tutor based on the tutors email. Then, from these requests, I had to figure out if there is any one request which has been accepted but not completed. And, then I had to pass this value to the Tutors sheet. Since I could not detect which request is the current request, I just passed all the values of the Session Completed column to the Currently Not Occupied column in the Tutors sheet. If one request is not completed, the Session Completed column will be False. And, then I can detect if the tutor is not free by the condition “The Currently Not Occupied column contains False”.

The Currently Not Occupied column used the Google sheet formula: =JOIN(", ",FILTER(‘Connect With a Tutor’!J:J,‘Connect With a Tutor’!H:H=G3)).
The Filter function filters the Session Completed column(J) from all rows in the tutoring requests sheet (Connect With a Tutor sheet) based on the tutor email which is present in G column in Tutors sheet, and then joins them. So, if there are three tutoring request, of which all are complete except one which the tutor is currently doing, this cell will have the values TRUE,TRUE, FALSE. So, if this column contains a FALSE value, it means the tutor is not free to do another request. Thus, I have got a visibility condition right on the Tutors sheet. This is what I think you would like to have.

Now, on the Tutors Dashboard, I have a button Take a Tutoring Session which is linked to Tutors sheet. When tutor clicks on this button, on the Tutors sheet page, I have three components. One is a button Take a Tutoring Session now which will be visible only if Available is True and Currently Not Occuppied does not contain False. Next is a Rich Text message "As you are taking a tutoring request now, more requests will be available once you are done with the current tutoring request."which is visible only if Currently Not Occupied contains False. I have another Rich Text message "Please be available"with a Switch to make Available True. This is visible only if Available is not true.

Let me know if anything is unclear.

7

Great responses Gents!

I appreciate my post wasn’t the most coherent but thankful you were able understand my dilemma . I read your responses first thing and took the rest of the morning to digest.

My key takeaway was that your implementations had one thing in common, the use of tutor page @vijay and host page @Robert_Petitto for the visibility condition. I had already plumbed in the available/busy/offline status so my solution was simply to have a button on tradesperson page which opens an inline list of job requests.

The smoke and mirrors as @Robert_Petitto puts it was to make that new button visible when they tradesperson’s status is set to “available” and viola problem solved… almost.

I noticed in testing that the nature of navigation through pages in Glide means that on their way back to their profile page they will see the inline list again! As it’s an MVP this works just fine for me.

Even in production it’s not too much of a problem as it’s really clear on the tradesperson profile between open and closed jobs. it wouldn’t scale well I know but it’s a good start. will have to make it clear that having more than one job open is against the rules!

Thanks so much @Robert_Petitto and @vijay for coming back to me! Your input was highly valued and much appreciated!

2 Likes
8

How is the app going? I’m also looking into building a tutoring app and doing some research. We have a company but would love to save time on connecting tutors,booking sessions, receiving payments,etc.

9

Sorry, the work got abandoned and haven’t done much further on it.

10

How far have you gone into making the app? Do you need some help?

11

As an update i haven’t worked much on it since May but resume work on the app recently. This time, I’ve been working on implementing user profiles by adding row owners and also have everything in one app (when I started this thread there were 2 separate apps sharing the same spreadsheet).

I’m struggling because row owners has effectively broken the app. I think row owners work as advertised, but my workflow particularly no.6 no longer passes the client’s information to the tradesperson’s screen. No matter how much I play around with lookup values and relationships, glide prevents the tradesperson from having access to another users (or in my case the DIY’er name and telephone number).

From my understanding, row owners was created to make personal data secure which I’m all for. However, for my use case, I need specific user information to be shared with another user in a controlled way. i.e. once the DIYer makes payment, the tradesperson gets the correct DIYer’s information.

I was hoping someone may point me the right direction

12

With row owners, from what I understand, you essentially have each persons information in a “vault” that only they can access.

With your Step 6, when the DIY’er makes a payment, which I assume involves clicking a button, can you pass the relevant detail (their telephone number) on at that moment. I expect that you can copy data from that vault at the users request. It could pop up with a reminder, or your T’s&C’s just need to mention that relevant personal data is passed on when required for transactional purposes.

As for the original part of this thread, I am having a similar issue but I can’t see a way that Roberts solution solves it, I am unable to get the data to pass through the sheets i properly.

I have

User
*Skill (with unique ID) *
Log (user logs what skill they are doing) - records skill ID and user ID together.

On skill page I have a form link button (to register that skill to the log), which I would like to disappear once the user has logged they are starting that skill to prevent them logging evidence twice. I know if I can do this, then I can also display a “Complete” image once the skill has been certified completed.

Any pointers greatly appreciated, have been banging my head up against this for the last few days.

OK, all sorted. It was simpler than I thought. I think Roberts solution was for something a touch more complicated.

I used an IF-THEN-ELSE on the Log page to reference current user ID against the user ID in the log. Either TRUE or blank. I could then link the SKILL and LOG sheet through the SKILL ID. This then allowed me to check whether the IF-THEN-ELSE result was empty or not.

I was previously getting irritated that the ITE wouldn’t allow me to check anything other than EMPTY or NOT.

13

Just thinking about the above.

If I have multiple people signed into the app, does this cause a problem with the relations I have created.

If one of the signed in users creates the TRUE condition does this not occur for all users?

Or does the fact I am searching on the basis of signed in user mean that everyone will experience this uniquely?

There doesn’t appear to be a way that I can have anything user specific that would help me achieve these links between sheets.

14

If your value used for the relation is based on a user specific column, the it will be unique for each user. If you are updating a non-issue specific value to the Google sheet, then it will be shared among all users unless each user views a filtered unique row only for themselves, then it can still save to the sheet and only they will see the unique results.

15

The initial IFTHENELSE that triggers all the lookups and relations requires a check on the Current User ID matching the USER ID in the log. Whilst that USER ID isn’t a user specific column, it is specific to the user viewing at that moment.

I guess it isn’t something I can test without upgrading to a plan and getting someone else to test at the same time as I am logged in.

If it is a problem I am not sure how I can get around it, as I can’t find a way of having a user specific row within the ID confirmation process as I can only use that feature on basic columns.

16

What does that If Then Else column mean in your context, can you elaborate it more on how it affects on the lookups and relations? Sorry I’m not picturing the whole flow here.

I don’t think upgrading is required, you can just switch users in the editor.

I have a sense your problem can be perfectly solved through user-specific columns.

17

Screenshot 2020-11-15 at 15.52.17
Screenshot 2020-11-15 at 15.52.171947×429 118 KB
Screenshot 2020-11-15 at 15.51.37
Screenshot 2020-11-15 at 15.51.372241×543 271 KB

User link on left of top image is a relation into the user profile page which has a unique user ID.
User check, is ITElse on USERID = Logged in USER

Second Image

SkillLogLink links the two sheets you see. USER checkup looks for any data in UserCheck

Visibility of an item is based on this info.

The Signoff check works in roughly the same way

18

What is this part specifically for, why do we need a check here?

19

Screenshot 2020-11-15 at 16.08.32
Screenshot 2020-11-15 at 16.08.32544×1092 20 KB

The Submit Evidence button needs to change to a submitted button once the complete the form or a completed button once the assessor signs it off as such.

If “Bob” is looking at that page then he will see the word complete. If AN Other is looking, then they see the submit evidence button.

/edit Probably should explain that this is from the SKILLS sheet, and the form it links to updates the LOG sheet (although you probably figured that out).

20

I imagine you can do this.

Each skill has a rowID, which you submits to the log alongside with the evidence.

In the Skill sheet, make a template column with the form of:

{Signed-in user’s email} - {Skill ID}

Then in the Log sheet, make a template in the same manner.

{User’s email} - {Skill ID submitted}

Make a relation matching the two, if the relation is not empty then show “Submitted”, else “Submit Evidence”.

Would it work?

You can add a boolean if you need verification. That makes the first {Signed-in user’s email} - {Skill ID} - True.