My App helps my users to manage all the Digital Signatures they have of their clients. When a new New Digital Signature entry is made through Form Submit option, they can select the “Possession” of this item (e.g. With Client or With Us)
Sometimes, this item can be handed over to their Client or vice versa (sometimes, more than once), if required. As per my knowledge, we can do this using “Edit” option by editing “Possession”. However, I want to record log of movement of this item so that, it can trigger an email to client about movement.
I understand that this can be done through Form but not sure how it will work (Sorry, not that expert). Please let me know if there is best way to do this.
Instead of having a column for possession in the same sheet, create a new sheet for possession logs.
In that sheet, have columns of timestamp, item SKU (Unique ID of item), customer email and possession and then create a multiple relation of this sheet with your items sheet using the item SKU.
That way each movement will have its own row using which you can send emails and you will always have a log for the overall movement of an item.
Thank you Manan for the detailed explanation. I will do that and will revert how it will work.
Can you also tell me, how would I give them an option for recording possession when they add New record? Like, Either
I make it default to have possession “with Us” at initial stage. or
Can I give them an option, once they submit form for new record, in the next screen, they get a button to add Possession.? If this is possible, how to achieve this?
I would be happy to have your recommendation. Thanks
For the first time they add a new record, you could give them a choice component but don’t let them edit it after they submit. That way you know the initial status of the item’s possession.
From the 2nd time onwards, if they want to change the possession, they have to create a new record. That can be achieved using a form button that points to your new sheet.
@Manan_Mehta, than you for all your suggestions. I have implemented the Movement Log in different sheet.
I want to extract the latest Possession from Movement Log Sheet to Main Sheet. Can you help me with that? Since the Glide Table is live, can we achieve this in Glide Table itself?
For e.g. For Item X, there are three movements in Movement Log during last 15 days, based on the last movement, the possession is set (i.e. last movement of Outward, so the Possession is “Not with Us”. Now, I want to get this latest “Possession” to link to Item X in Main Sheet.
I hope you understand what I am trying to explain. Your help would be appreciated.
I assume you’ve included date in your movement log?
If you only need the last possession, you could create a relation to the movement log and use the single value column to to get the last matching possession from the last row in the relation.
If you want to obtain the entire last matching row (latest possession), then you can first create a single relation in the movement log sheet that links the Client, form, or whatever ID back to itself. This should give you all of the rows that belong to that client/form.
Create a Rollup column to get the max date.
Then create an If/Then column to check if the max rollup date matches the the date on in the current row. If it does then return ‘true’ as a value. This will mark all rows in the sheet that are the most current for the client/form.
Then you can either filter this list, or create a template of Client/Form ID and the ‘true’ value set in the If/Then.
Then create a relation in your primary sheet to the movements log sheet that links the (Client/Form - true) to the template set in the movements sheet.
Hi @Manan_Mehta, in continuation of this, I have a multiple relations (movements to the main item). However, when I delete the main item (this is necessary in my App for users), the movements log remains there in the Log sheet.
Any idea how can we achieve this @ThinhDinh@Jeff_Hager@Robert_Petitto ? (i.e. when I delete main item in sheet, it deletes all corresponding movements in Log Sheet). Can Compound Action will help here?
As a temporary solution, using visibility condition, I am just hiding the movement log when a user is deleting the Main Item. However, if you can help with the script (I am completely new to Script), kindly let me know.
Thank you