Mirror Calendar Table to another table

Ben_Rix · 02-28-2021 07:54 AM

Hey everyone,

Im in the process of linking my table “Sessions” to my google calendar table. The aim is to have the google calendar entries update on individual booking changes and to also delete entries from the google calendar when events are cancelled.

My aim is to create an action for an add a new row to another table using values in this row. This will be from the “sessions” table to the google calendar table. The idea is then to create a workflow to watch for changes on the start DateTime column to trigger changes. I also have a cancellation column in my “sessions” which has a TimeStamp action when the status of a session is changed to cancelled. The idea is that this will then be triggered by the google cal table to delete the event.

So I’ve started to implement this by using the Title field in the google calendar table as a reference to the sessions table, but, when I use anything that contains numbers and letters it says "Value in field cannot be converted to type Ref. - Does anyone know why this would be?

The main reason I have written quite a lengthy message to describe what I’m doing is to find out whether I’m doing this totally wrong or whether I should be approaching it a different way?

Regards,
Ben

Marc_Dillon

Please show the expression of what data you’re putting into the column. Also shown the column definitions for the referenced table, and the calendar table.

Ben_Rix

Hey Marc,

I just wrote out a lengthy reply and the penny dropped.

I was trying to reference my session table using a concatenated value which wasn’t the KEY for the table, a silly mistake…

I was hoping to use the title column as the reference but this really needs to have the clients name. So I’ve resorted to using the description column as the ref, but this is a bit of a shame as I was also hoping to use this.

As I am only allowed to add virtual columns to the calendar column, I don’t really see any other pre-existing redundant columns in that table to use for the ref column.

Regards
Ben

Marc_Dillon

Is there a way to use a VC in the calendar table as the Ref column?

MultiTech

This is the only way to really make this work…

While this would work… this is the sort of SELECT() virtual column that needs to be avoided.

This is the major limitation with working with an actual calendar data source - very limited and you can’t really do that much with them.

Ben_Rix

Well to be honest, id come to the conclusion that i really wont be needing that description column. The whole point is just for a quick visual reference anyways, and for the clinicans to use the google calendar to reference things they put in the app and things that wont be in the app such as free slots or personal events.

Ben_Rix

I dont think it would be an issue using a virtual column as a reference, but, I have an action that adds the ref to calendar table of the session table with an adds only workflow.

Problem is that the virtual column will only allow app formulas. At the time, i couldn’t think of another way to reference the sessions table from calendar table with an app formula.

Marc_Dillon

Can you match the record using the duplicated data? Presumably you’re recording certain things like; a name, a location, a date, a time, etc.

ANY( FILTER( Sessions , AND( [Date] = [_THISROW].[Date] , … ) ) )

Ben_Rix

Yes, of course. Thanks so much Marc.

Ill give that a go. Im sure i can get it to work that way. Im currently working on @MultiTech_Visions looping action for some invoicing automation.

Regards
Ben

MultiTech

It’s not my handy work, @Steve was the mastermind behind that.
I just made a video showing how to do it.

That teamwork though…

Ben_Rix

Yep!!! you guys sure do an amazing job, thanks for all your hard work to help us appsheet newbies. I really appreciate it, thats for sure.

Steve

DevM

Hi there… would you mind sharing your video?

MultiTech

This was in reference to a “looping with actions” video - not anything about mirroring calendar tables.

MultiTech

Instead of trying to link the calendar to the sessions, link the session to the calendar - reverse the ref.

This way the reference data is stored inside the Sessions table - and not being calculated over and over in the VC.

Yeah, it’s a little backwards… but hey, when you’re working with a calendar data source… you gotta do what you gotta do.

So inside the Session, have a ref to the calendar table.

then use an action to do the lookup to find the appropriate calendar entry and mark that as the ref.

You’ll be able to deref entries from the calendar into the session… you’ll be able to easily tell an action (run an update on [this] calendar entry), etc.

Ben_Rix

Thats the way i have it matt, from the app to the calendar. Otherwise that would be some whole other mess i definitely wouldn’t be able to get my tiny little brain around haha

MultiTech

No no… in the app… on the session table… make a ref column that links to the calendar.

With this, you’ll end up with a reverse reference of sessions on the calendar

You’re trying to record: “this calendar entry belongs to THIS session” by recording the session link on the calendar.

Instead… let the calendar entries live as they be, mark the sessions as belonging to the calendar entries.

Ben_Rix

Its certainly does the job, whilst saving the £240 id be paying for the zapier service.