[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.

[Ptt_Pch]

The difference between the 2 seems to be in the {Name} field.

In the first screenshot, the record were you linked all the others have is not named, in the second screenshot it is :winking_face:

You can, I think, just copy the name of the single record from the 2nd screenshot ( :white_check_mark: ) and past it in the {Name} field of the base in the 1st screenshot.

[W_Vann_Hall]

As far as I know, even if the links are to ‘unnamed record’, the de-duplication routines should work. Could you post or PM me a link to the current version of the base for me to check?

I assume the CSV import file you sent me earlier is the same format as you’re currently using.

Vann

[M_k]

Hi

I set up a base using the De-duplication Routine, but I had to copy and paste the checkmark (helpful suggestion) into the first field in Table 2 and then there were checkmarks showing up in all the records in Table 1 (Dupe Check field) and for all records in Dupe? field too, so I don’t know how to correct this. If I didn’t copy the checkmark, I would get no checkmark/s at all and I tried many times. I should also mention that I am using the free version of Airtable on my MacBook, High Sierra 10.13, so I don’t know if this makes a difference in terms of the checkmark showing automatically, as opposed to another version, were it does show up automatically or if it’s the set up sequence. The sequence I used is: I linked the two tables first, then I set up the Rollup features and added the formulas accordingly, then at the very end, I would link all the records from Table 2, so I hope I did that right.

I copied and pasted a second set of records, to see if the de-duplication worked, but there were just checkmarks showing in all records in Dupe? field, which worked I suppose, it’s hard to tell with all the Checkmarks. :grinning:

The CSV import file that I emailed you is the file that I have been testing with.

This is the link to the base that I was describing above:

Airtable

De-dupe TEST checkmarks - Airtable

Explore the "De-dupe TEST checkmarks" base on Airtable.

I have also included a link to another version of the same base as above, except it is before I copied and pasted the checkmark, so you can see an unadulterated version and I also copied the same records twice, to see if the checkmark would appear automatically, but it did not:

Airtable

De-dupe TEST BEFORE COPYING CHECKMARK 3 - Airtable

Explore the "De-dupe TEST BEFORE COPYING CHECKMARK 3" base on Airtable.

Thank you very much for your help and patience while I work my way through this.

MK

[M_k]

Hi

I am very curious to know how the De-duplication Routine bases that I created (included in Reply# 23) are working or not, or did I miss something. Although, if I did do everything correctly, but I am still not getting checkmarks in the correct fields’s and tables, automatically, I am wondering if it might have to do with using the mobile version and on MacBook. A good test would be to have this base set up with a mobile app and on a computer, independently, to see how it works. Just a thought.

Thank you,
MK

[M_k]

Hi

I am hoping to start using the De-duplication Routines for a bunch of finalized bases that I have already created, so I was wanting to start using it for 5 bases.

I was wanting to settle this issue, so I can go ahead with finishing my bases.

Thank you,
MK

P. S. I realize that you are busy, so I was hoping you could fit this in. :grinning:

[M_k]

Hi

I wondered if someone could take a look at Reply #23. I realize that this De-Duplication Routine is someone else’s, I just wondered if another set of eyes to take a look, to check if it’s something that I missed, since it’s not working.

I sent a PM to W. Vann Hall on a few occasions, but I think he is quite busy. I would like to resolve this issue, one way or another, since this query has been left in limbo for awhile now.

I wanted to have something to detect duplicate records in a base that I imported a CSV file and I wanted to import a second CSV file, but when I do that, all the records are duplicated, so I wanted a way to visually see what the duplicate records are, so I can delete them.

The sequence I used is: I linked the two tables first, then I set up the Rollup features and added the formulas accordingly, then at the very end, I would link all the records from Table 1 into Table 2, so I hope I did that right.

I have the free version of Airtable and I use both an iPad (10 & 11) and MacBook (High Sierra 10.13) with Airtable version uploaded.

I am really hoping that someone can really help me, since I am stuck.

Thank you,
MK

[M_k]

Hi W_Vann_Hall

Well, I can now say that I finally figured out how to do the De-duplication Routine.

I still have to copy a checkmark, just to kick start it, (it needed a bit of a kick), but once I copied it, the checkmarks are showing in the right places. It seems that I should have linked the records first, then create the Rollup features, with the appropriate formulas. Once I figured that out, it works now. I even took it two steps further, by creating a view showing all records with checkmarks. I also figured out, when I am working on my iPad, if I am just entering a few records, I can copy a checkmark and paste it in the Dupe Check column, instead of filling the column on my Mac. I did these two things all by myself. :grinning: Pretty good, eh!

Thank you for all your help.

MK

P.S. If I can only figure out the formula in the Integromat scenario, that I am working on, then I would be really flying.

MK

[Cameron_Deleone]

In case it helps, the dedupe block is now live and open to all Pro users. For more info, check out the support article: https://support.airtable.com/hc/en-us/articles/360008332253.

This topic will automatically close tomorrow.
Enjoy!