Hello,
I’m new to Glide and loving it!
I’m creating an app where students can borrow cameras and other equipment.
They’re allowed to borrow things for up to two days.
I’ve made it so that they can book only two weeks in advance.
Here are the problems:
How do I show the dates that a certain item is booked?
When I use the calendar list view, it only shows the booking on the start date. So if the item is booked from Tuesday to Thursday, it only shows up as booked on Tuesday. How do I display it in the calendar list under all the days?
When a user wants to check availability, they enter the start date, and how many days the booking is for: one or two. The end date is calculated in the sheet. How do I look up availability for all those days? So if they want to book from next Tuesday to Thursday, and the item is already booked Monday to Tuesday, I want it to say that the item is not available for that booking (and ideally make the “book now” button disappear).
I’ve seen solutions using filters that assume that there is only one booking in the system and check if the date is before or after that date, but in this case, the item could be booked many times in that same period, so I need to compare the range they want with all the booked dates. How do I do this? I thought I could create an array that lists all the days that the item is booked, compile them into a comma-separated list, and then do the lookup there, but that didn’t work.
Any suggestions for either of these problems would be great!!!
I’m not sure if you add an end date that is not on the same day as the original date then that would show up as multiple days on the calendar view, but make sure you add both a start date and an end date to the calendar.
Regarding the second question, what I would do, albeit a bit complex is:
For every booking, convert the start date and end date to a numerical value. This can be a date difference between that date and something like 1st January, 1970, so you have a continuous set of numerical values for the next set. Let’s call those “start diff” and “end diff”.
Next, use a JavaScript snippet to generate a list of numerical values between “start diff” and “end diff” that also includes the “start diff” and “end diff” value itself. Let’s call this “booked list”.
When users want to create a new booking, you create a custom form, create a relation from the item ID that they chose to the list of IDs that have already been booked, return a joined list of “booked list” values, comma-delimited.
Allow users to choose their start date and end date. Repeat the “start diff”, “end diff”, and “booked list” column for these two values.
Now, you have two comma-delimited lists, join them using a template column, split them into an array using a split text column, and use the “Duplicated Items” column to check if there are any duplicated values between the two arrays. If there’s at least one, don’t allow them to book.
Just off the top of my head, there would be better ways to do it.
It sounds like it would work, but I don’t know anything about Javascript and it goes way beyond the scope of my project!
Here’s what I’m trying:
When someone checks to see the dates an item is booked, they enter the item, start date and number of days they want to book for, one or two. I have a few things going in Google Sheets that help me to alter their input to our specifications (you can’t book Thursday to Monday, or Friday to Tuesday) and it spits out the end date. A unique transaction ID is also added to their user profile.
Then I have three columns in the booking responses sheet that calculate day 1, day two, and, if necessary, day 3, as separate dates.
When they make the booking, it takes the start date, number of days, item, and transactionID from their user profile, drops them into the booking responses sheet, and those other things are calculated. This takes a few seconds, so I wasn’t able to do it all in one button, so they get a confirmation screen, in which day 1, 2 and 3 are listed, and when they confirm it adds 1 or two rows to another sheet which contains each booking as an individual day. So if they booked for one day, there are two entries with the same item and transactionID, one for day one and one for day two. If they booked three days, there are three entries.
Then there’s a relation column under each item that lists all the days booked for that item from that sheet. This is what I’m hoping to use as a lookup to see if any of the calculated dates that they want to book are in that list, and that will determine whether the “Book Now” button will show up.
Wish me luck!
I’m already having problems with date formats… I’ll see what happens.
I will definitely do that next time. I put a lot of functionalty in Arrays in Google Sheets, then added some lookups and relations in Glide. It works great but it’s sooo slow! You live and learn!
We present our project tomorrow, and we’ve built something we’re really proud of!
Bonjour,
Je suis en train de mettre en place votre solution (transformer dates de début et fin en entiers, et générer la liste de tous les entiers entre celui de “date début” et celui de “date fin”.
Ensuite je comparerai les valeurs pour éviter des réservations qui se chevauchent.)
Mais, j’éprouve quelques difficultés avec le code java script.
Voici ce que j’ai fait, mais je n’ai pas de résultat:
La date de fin est identique (mais supérieure à la date de début).
Aurais tu une idée de ce qui ne va pas?
Je n’ai jamais utilisé la colonne Java Script, peut être que j’ai fait tout de travers…
Merci pour ton aide!
There is nothing in your javascript column to call the genererEntiers function, so nothing is ran. For simplicity remove the function line and the last line so the remaining code can run.
Merci pour votre réponse.
J’ai essayé d’enlever la fonction et la dernière ligne.
Je n’ai pas de résultat, et j’ai un message d’erreur que je n’arrive pas à résoudre…
Alors j’ai cherché comment fonctionne le java script et j’ai compris que définir une fonction ne veut pas dire qu’elle s’exécute. Il faut que je l’appelle… J’ai donc essayé d’appeler la fonction, mais je n’ai pas de résultats non plus (je n’ai surement pas la bonne façon d’écrire tout ca…)
Avez vous une idée de ce qui ne va pas? auriez vous un exemple de code qui fonctionne dans les colonne glide? est ce que j’utilise correctement p1 et p2 (j’ai supposé que c’étaient des variables…).
Pour rappel, j’ai besoin de tous les entiers qui se trouvent entre deux entiers.
OK, your second version with the function is close. You just need to make one small change.
Replace
generer();
min = p1;
max = p2;
With this
return generer(p1,p2);
What you are doing is passing the p1 and p2 parameters into the function when it’s called. Then you need to add a ‘return’ on the function call to pass the value back to the app.
Try that and see if that gives you a value. Javascript in Glide usually only returns text, and you are trying to return an array, so it might not work. That might be why it didn’t work when you removed the “function” ealier. Make the change I suggests above and let me know if you still have a problem. I thing you might, but it will be an easy fix.
