Unable to trigger an appscript onEdit after glide form submission

Ask for Help
21

That’s kind of clear, although I’m not really understanding why you would need a separate sheet for Bottles.

I have some questions:

  • Is each users wine collection private to themself, or will users be able to view the collections of other users?
  • Can Cellars be shared by two or more users, or is each Cellar private to only one user?
  • Could I as a user have the same wine in multiple locations in my cellar? For example, I have two bottles of wine X in location A, and 3 bottles of wine X in location B?
22

Users wine collection will be private for themselves.
Same goes for the cellars, one private cellar per user.
Yes you could have the same win in multiple location in my cellar, I think this is why I’ve added a “Bouteilles” sheet.
For instance I had a wine “Wine 1” for which I have 6 bottles. I want to be able to place the bottles in spot “A.1.1”, “A.1.2”, “B.1.1”, “B.3.2” … And to empty the spot when a bottle has been drunk.
I want to be able to determine for “Wine 1” how many bottles there are, and where they are located.

23

I forgot to say that I have not created sheet or functionalities yet to make it a multi-user app but it’s the plan. I first wanted to make sure it was doable for one user.

1
11124×487 26.9 KB

And I want to display a screen (see screenshot) where the user can visualize its cellar. If users click on an empty space (green + button) then a pop form allows them to choose a bottle that has been created but has not been placed into the cellar yet (see second screenshot). If users click on a bottle then I want to display the details of the page.

Thanks for your help Darren !

24

image
image914×340 28.6 KB

The pop up that allows user to place a specific wine

25

Okay, so here is my suggestion:

  • When you add a Wine, also add the number of bottles at the same time (maybe that’s what the “quantiteAchat” column is?)
  • In your Wine table, create a multiple relation column that matches the WineID with the WineID in your Cellar
  • Add a Rollup column, that counts the WineID via that relation. This will tell you how many bottles of each Wine you have in your Cellar.
  • If that number is less than the total number of Bottles of any Wine, that means you have extra bottles that are not yet in your Cellar
  • So in your popup that adds a bottle to your Cellar, you can use your Wine table as the source of the choice component, and filter it where “bottles in cellar is less than total bottles”
  • Then when you remove a bottle from your Cellar (after drinking it), you should clear the WineID in the Cellar row, and also decrease the total count of that Wine by 1
  • To be able to decrease the count by 1, you should create a single relation in your Cellar table that matches the WineID with the WineID in your Wines table. You can then use an Increment Action (-1) via that relation when a bottle is removed.

I think that should get the job done for you without the need for the Bottles table, and no messing around with Apps Script :wink:

1 Like
26

Hi Darren,

That’s great Thanks. I set in place your suggestion, but I struggle with one last thing.

I want to keep records of the quantiteAchat (your French is good ! It’s the number of bottles) and I can’t do that if I use this value with the increment action.

So I have tried in my add form (add a wine) to have the number in quantiteAchat copied in my new field that will be used for the increment action. To do so, I used the set column values on submit where the new field = quantiteAchat.
But it’s not working, Am I doing something wrong ? Do you have any workaround for me ?

Thank you so much for your help

27

Instead of using an onSubmit action, you probably need to use a special value with the form.

Question: Do you have User Profiles configured, and if yes which table is your User Profiles table?

The easiest way to do what you want is to store that value in your User Profiles table, then you could pass it as a special value with the form submission.

28

Isn’t onSubmit action supposed to work for my situation ?

I haven’t configured my user profiles yet, but I have a table with name and email.

29

I usually avoid On Submit if I can. Especially if you are attempting to update a row that was just added. In my opinion, there is to much reliance on the row being added timely before any updates are performed. If the row has not finished adding when the Set Column action is ran, then there is no row to update.

1 Like
30

OK I understand. The only way to do what I’m trying to achieve is with the special value from the users’ table?

31

Not the only way, but probably the simplest way.

32

OK, and how is the easiest way to configure users’ profile, to implement your suggestion while taking into account that I’d like for each users to view and edit their own (and only their own) wines and cellar ?

33

The way to do that is with Row Owners.

So in your User Profiles table you would apply Row Owner to the email column, and then in each table where data needs to be kept private you will need a column to hold the users email address, and this column should also have Row Owners applied. That column can be populated with your form by using the Users Email address special value..

With the above in place, each user will only have access to their own data.

1 Like
34

Hi Darren,
Sorry I haven’t been able to work on it until now.
I have configured User profiles and I have created a column to hold the users email address in the relevant tables where I want the data to be private.
Yet I’m still struggling to fill in the quantiteAchat and the stock within the same form (add a wine) using special value.
I don’t know how to proceed. Could you please help ?
Thanks