Hello Gliders !
I have a use case that is a little bit tricky and I’m wondering if there are solutions I’m not thinking of 
Basically I’m building an app for a non profit organization and I have 2 specific tables :
- Beneficiaries : People who will receive gifts and food baskets
- Volunteers : People who make calls and deliver packages to the beneficiaries
I have adress columns (streect, city, postal code,…) in both of the tables.
I would like to be able to build a relation between a beneficiary and a volunteer (so that we can assign a beneficiary to a volunteer who will be charged to deliver the gifts/food).
The tricky part is that the relation (in the Volunteer table) should only find Beneficiaries that are located at a distance lower that 10km.
I see a way to do that by creating a table that would store all possible relations between cities and their distance (using the distance column), but I don’t think that would be scalable. It would require a huge amount of rows with time.
I am about to check if I can find a solution implying Make and the Google distance API but I would definitely prefer to keep the logic in Glide.
Thanks for your help guys !
Question: Should Volunteers be automatically assigned to Beneficiaries, or would you have some sort of Admin user review them and assign manually?
Either way, I can think of a solution. But the solution would be slightly different in each case.
The automatic solution would definitely be preferred.
Of course it would be good that a standard user or admin is able to manually update the assignation afterward if necessary (example: if volunteer is not available after all, we need to be able to manually change the assignaton)
You’re a genius @Darren_Murphy
oh, one more question…
Can I assume that Volunteers are users of your App, and will be signed in?
If they are, can I also assume that their User Profile row is related to their corresponding row in your Volunteers table? (or is it the same table?)
It’s still to be defined, but so far it’s not planned to have all the volunteers as users in the app,
We’ll just have a few users that will manage the system, create and update information.
Would that be an issue ?
Not really an issue, but it does beg the question - how would a Volunteer know which Beneficiary they’ve been assigned to?
Anyway, that aside, you could do something like the following to allow Volunteers to be assigned:
- Make sure both tables have a RowID column, and use those as VolunteerID and BeneficiaryID
- Create a Glide Helper table, and add a User Specific text column. This will hold a BeneficiaryID
- Create a Details Screen using the Helper Table as a source
– Add a Choice Component, use the Beneficiary Table as a source, and write the BeneficiaryID to the User Specific Column
- In your Volunteers table, add a Single Value Column. This should take “First->User Specific Column” from the Helper Table (ie. the selected BeneficiaryID)
- Create a single relation that matches the above single value column with the BeneficiaryID in the Beneficiaries table
- Now use a Lookup column to fetch the Location of the selected Beneficiary via the single relation.
- You now have two location columns in your Volunteers table - one containing the location of each Volunteer, and the other containing the location of the selected Beneficiary.
- Use the Distance to Location column to calculate the distance between each Volunteer and the selected Beneficiary.
- Now you can use an if-then-else column to create a filter:
– If Distance is less than 10km, then true
- Back on your details screen, you can add an Inline List, using the Volunteers table as the source, and the if-then-else column as a filter.
- This will give you a list of Volunteers that could potentially be assigned to the selected Beneficiary.
From there, it’s just a matter of deciding what you want to do with the list. You might have an action that writes the VolunteerID into a column in the Beneficiaries table, indicating that Volunteer has been assigned to that Beneficiary. Maybe you’ll also use that same column as a filter for your Choice Component, so that only those Beneficiaries that don’t have assigned Volunteers are listed.
Anyway, hopefully that gives you some ideas to think about.
4 Likes
Hey Darren,
I think I see where you are going and I love it !
I need to test on my side and I might come back with a few questions.
But anyway thank you so much !