How to create publication dates for different content platforms and look up in date field

Hi @David_Nunn,
I think that would be the best option to control the dates that are selectable.

Create anther table of dates and their platforms, then let the user link them. you can also put conditions on the linked records so that only the appropriate platform dates can be selected.

Many thanks for taking the time to reply.

I have a linked field joined to a Platform Dates table, but it pulls through dates for all platforms.

What am I doing wrong?

https://www.awesomescreenshot.com/image/26880675?key=20ca321604f42eefceb75d629bda30cd

Hm, you’ve already limited the records available selection to a view?

Yes, i did that. After much searching i discovered that Jotform would allow values to be passed from one form field to another to facilitate a filtered drop down. I haven’t tried it yet

Roger that, hope you manage to get it working man. I’m pretty confused by the fact that all the dates are showing even though you’ve restricted it to a view that only displays a subset of the dates. I’m probably missing something.

If I’m understanding your workflow correctly, you want the following to happen:

1. User selects Platform 1
2. User is only shown dates that are applicable to Platform 1 and selects one from there
3. User selects Platform 2
4. User is only shown dates that are applicable to Platform 2 and selects one from there

I was thinking that if your users are adding stories to the platform via a Form, you could achieve the above by doing the following:

1. In the Platform Dates table, you have one view set up for each platform that will only display the dates for that platform
2. In the form, you have a single select option field which allows the user to select the platform they want
3. In the form, you have multipled linked fields, each one set to a different view. These are all set to be default hidden, and set to display only if the corresponding platform option is selected in the field we set up in step 2

Best of luck!

Thanks so much for your suggestion, Adam!

This approach would allow users to enter the data. They also need the ability to edit dates, so…

Is there a way to open the same form to edit items? For example, in the standard expanded record view, could the user choose to open the item in a form?

Hmm, so if the user selects the platform, they would not only see the dates applicable, they would also need to modify those?

E.g. they select Platform 1 and see the dates “23 May 2022”, “24 May 2022”. At that point, you want them to be able to change “24 May 2022” to something else, such as “25 May 2022”?

Exactly!

For some types of content platforms - e.g. webinars - a date picker is fine because they could be held any day of the week.

But email newsletters will go out once a fortnight. So a story had to be put back to the next newsletter, you would want the user to accurately select a date a fortnight later.

Ahh, I see. I’m afraid I don’t know of any functionality within Airtable Forms that would allow you to do that.

Would it help if we figured out a way to make sure the correct date (i.e. a date a fortnight later) was available for selection, no matter what date the user was using the form?

Apologies if my suggestion isn’t helpful; I’m somewhat confused by what we’re trying to do here. It appears we’re limiting the dates that users are allowed to select based on the Platform, but we’d also like to give them the ability to create new dates as well, and the two things seem to be in conflict with each other?

Again, thanks so much for your comments.

The goal is to only have the correct dates for each platform available for selection.

The user will not be able to create new dates. But they may need to change the date initially selected when creating the record.

Here’s a quick video explainer using our current content platform.

https://www.awesomescreenshot.com/video/9045557?key=b41a5e695ed4db274ccf163129fac0be

The current software actually makes creating a single record really easy. So you’re probably wondering, why change to Airtable :slightly_smiling_face: ? That’s because we need to do csv file uploads and integrate with other time-consuming processes, such as finance… If I can achieve a passable data entry/editing experience, we will gain a lot from Airtable’s other features.

Hi David, thanks for the video! I have a very clear understanding of what we’re trying to achieve now.

Just so we’re on the same page, it appears we’ve successfully solved the issue where we limit the dates appropriately for each platform, and we are now trying to solve the issue of allowing users to modify previously entered data.

I am assuming the limitation we’re facing is that not all of the users will be collaborators in the base, and thus we need them to be able to make edits via Forms instead of via the table itself.

Unfortunately, we are unable to edit records via a Form.

However, there are workarounds for this, and they all basically involve us having two tables, one of Form submissions (let’s call this “Form Submissions”, and the other of formatted data (“Data”) compiled from “Form Submissions”

Users would use one form to create records in “Data” and to modify records in “Data” they would fill out a form that would create records in “Form Submissions”

From here, we have some options:

1. Users are given a form where they can select the record from “Data” they would like to modify, and submit the new data they’d like to use to replace it, and we use rollups and lookups to handle data formatting

The pitfalls of this method would be that your users wouldn’t have a good experience trying to find the record they need to edit, they would not be able to see the record’s current data, and they would have to fill in all the fields that the record previously had filled, not just the one they want to modify. Consider: They just want to modify the “Blog Date” field, but they also have to ensure that the “Platform 1 Date” field is also filled in with the previously set value

2. Users are given a form where they can select the record from “Data” they would like to modify, and submit the new data they’d like to use to replace it, and we use scripting to handle the data formatting

The pitfalls are similar to the previous option, except that users will not need to fill in all the previously set values, and also you would have to maintain a script

3. Users are given a shared view where they can see all of the records in “Data”, and are able to click a button (maybe “Edit Record”) that would open a pre-filled form, and we use rollups and lookups to handle the data formatting.

The pitfalls of this option are that your users would be able to see all of the records in “Data”, and I’m unclear whether we’re not letting users modify stuff on the table directly due to them not being allowed to see the data or them not being collaborators. Personally, I think this is the nicest one and recommend it

I’ve created option 3 for you here. You can duplicate the base via clicking the title of the base and then the three horizontal dots.

Even without duplicating it, you can test it out by going to this shared view link. When you click “Edit Record”, a new window will open with a Form prefilled with the record’s current data and the record automatically linked. All the user will have to do is modify the specific field they need to change.

On submission, the “Data” table will update automatically.

Here’s a gif of it working:

Let me know if you have any questions or if you would like to explore option 1 or 2

Thanks very much for these ideas!

On first read, I was a bit confused, so I need to have a proper look when I have time to get my head around this.

I also tried the demo link. It seemed to me that you would have to manually refresh the grid after making changes - is that correct?

I think if the workaround gets too confusing it may be better to let people struggle with a date picker.

Yeap, that’s right. Shared views need to be refreshed to see changes I’m afraid

Hah, fair enough. Happy to help you set that up if you’re keen though!

I think I have a way to solve my user interface issue, without elaborate workarounds.
I link to a view with only recent/future dates, I can have users search for the platform name to pull up the options.
https://www.awesomescreenshot.com/video/9147495?key=b5594489fe53b74c85434d3490f6a382
I have a solution for platforms that require daily dates, but no need to go into that.
My problem is that I need to be able to bulk import stories in a CSV file) and create linked records for platform and date.

CSV file import doesn’t allow values in Linked Record fields.

Is there a way to do this with an automation? Maybe, creating an import table and then running a script from it to create records in the stories and platform dates tables?

Glad you figured something out!

Yeah definitely. With reference to the example data you provided, for the row: 7 great TED Talks to watch to while you’re studying AAT, you would want:

1. Two new records in the Platform table

• Record 1: AAT
• Record 2: Comment

2. One new record in the Platform Dates table

• Record 1: 02.06.2022

Is that right?

Thanks Adam! Your summary is very close. It would actually be:

Point 1 - correct
Point 2 - if the record exists already in Platform Dates, link to it. If it doesn’t exist, create it.

Roger that

Hmm, in your CSV, is the data in the Platform column comma separated? So with reference to 7 great TED Talks to watch to while you’re studying AAT, is the text in the Platform column the following?

AAT, Comment

If it is (or if it isn’t but you can make it export like that), then we could just convert it into a linked field instead of having to resort to automations