[SOLVED] Help With Staging A Base

[M_k]

Hi

I have a situation with my Airtable base and I wondered if anyone can help. I realize this post is a bit lengthy and with a number of steps. Your help would be greatly appreciated.

The formula that I used is, if it helps:

DATETIME_FORMAT({DATE ISSUED},‘ddd, MMMM D, YYYY’)

I was sent this to resolve the issue of keeping the date format when I import a CSV file. I have about 1,400 records and I don’t really want to manually change the dates and I do have a date formula, but I am having a bit of a hard time following these steps. I have about 14 fields in my base. I should mention that I was able to create a view with “Fixed Date View” field with the formula, but I don’t know what to do next, (the view has all 14 fields), since the base is not working properly (I cannot add any other imported CSV files/records). I wondered if someone can help me with the rest.

I am planning on using an integration app to “catch” all duplicate records.

This was the suggestion:

To fix both of your issues at once, I would suggest creating a staging table for your imports with a few fields to make this process go more smoothly. I don’t know your exact setup but based a little off your screenshots lets say you have a table with a RecordID (to help identify existing records) as well as the fields of Stamp Description, Subject and Date Issued.

Create a staging table in addition to the table that the records will ultimately reside in. This staging table can be all text fields which is perfectly fine.
Add a formula to the staging table that converts your text date of “Wed., May 23, 2018” to an actual date field. Change the formatting of that formula field to not use time.
Create a view named “Fixed Date View” or something descriptive that uses this new date from the formula and hides the old text date.
You could at this point copy and paste from the staging table’s “Fixed Date View” to the actual table the records will be in.

[M_k]

I forgot to mention that I am using both an iPad with iOS 10 on one and iOS 11 on another and I have a MacBook with High Sierra, 10.13.

Thank you.

[Replate]

I’m not confident and don’t have quite enough time to check right now, but I think the CSV Import block will convert date strings from your .csv to date data types so long as the format is parse-able.

[M_k]

Hi

I am using the free mobile app, unless this block is free.

I also wanted to say that this option to set up a staging base was not provided by anyone from Airtable Community Forum.
Thank you.

[Ptt_Pch]

I’m sorry but what is your problem exactly ?
The import? The date? The filtering/view?

Could we just know a little bit more about what was your original problem?
What were you trying to do?
:thinking:

[W_Vann_Hall]

I have to say, I disagree with most of the suggestions you’ve received.

Unless there is some compelling reason you can’t have both a textual representation of a date in one field and the parsing of that text to convert it to a date value in another, the ‘staging table’ is an unnecessary complication: Import all fields as text, if needs be, and add formula fields to convert text into other data types as required.

Similarly, there’s no reason to use integration middleware for de-duplication — especially for as few as 1,400 records. The code I supply in the second version of my de-duplication routines can be integrated with virtually any base to provide automated duplicate detection and, optionally, removal, according to customizable rules and criteria.

You should keep in mind, though, CSV importing performed using core Airtable functionality — that is to say, without access to or use of Blocks — is a one-time process: Once a table has been loaded, subsequent attempts to import do not append or update records: They overwrite them.the CSV Import Block allows previously imported records to be modified, appended, or replaced, depending on configuration.

If you can provide a link to your base as it currently stands (preferably a read-only link to the entire base, with copying allowed), I’ll see if I can offer any advice or assistance.

[M_k]

Hi W_Vann_Hall

Thank you for your reply.

This all started whenever I imported a CSV file, the date field was in text and when I tred to format the field to date field I lost all the dates. With the formula, it works, but only for any new records, not the imported records.

In terms of creating a link and copying privileges, i would be interested in sending a link to my table, but I am not sure how to do that. Do you need the CSV file attached to an email/reply, too?

Thank you,
Mary

[Ptt_Pch]

M_k:

In terms of creating a link and copying privileges, i would be interested in sending a link to my table, but I am not sure how to do that.

There’s an easy way to do that with Airtable :winking_face:

Airtable - Support

Creating a base share link or a view share link

As an alternative to adding individual people as collaborators, you can create a read-only share link that allows anyone with the link to view (but not edit) an Airtable base or a specific table vi...

[M_k]

Hi

I am going to try sending a link to my Airtable base. Hopefully it will work. I just did a test to myself and seems to work.

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

i am interested in the option of setting up catching duplicate records that was mentioned in one of the replies.

Also, can I hide the date field without hiding the column that i enter the dates into?

Thank you,
I really appreciate your help.

[M_k]

Hi

I should also mention that the unique number or numbers/letters field/key is “SCOTT #” it will always have different numbers or numbers/letters. It is this field that I use, to check if I have any duplicate records.

Thank you

Thank you,
Mary