Help With Staging A Base


#1

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.


#2

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.


#3

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.


#4

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.


#5

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:


#6

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.


#7

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


#8

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


#9

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.

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.


#10

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


#11

As I recall, Airtable does not provide a graceful method of specifying date format when attempting forceably to convert a text field to a date field. Instead, I would suggest importing the date field as text and then creating a second field as a formula field using DATETIME_PARSE() to convert text according to a specified format. (DATETIME_PARSE() supports an extensive list of format specifiers, allowing you to match virtually any incoming format.)

To create a shared base link with copying enabled:

  1. Select 'Share'.
  2. Select 'Create a shared link to the whole base.'
  3. Select 'Private read-only link.'
  4. Make sure ‘Allow viewers to copy the data in this base’` is enabled.
  5. Copy the link provided.

Send me the link by PM or email ( wvannhall@paladesigns.com ); if you wish, you can attach the CSV field (or, even better, the top 3 to 5 rows of the CSV file :wink: ).


#12

Hi W_Vann_Hall

Thank you for your reply.

I copied a shared link to my base in reply #9. It’s the link:

“Airtable: Organize anything you can image”

I realize it was hard to find. I tested the link and edited and copied a record and it seems to work. If it does not, let me know.

In terms of the CSV file, I have one that has about 15 records. I will send it as an attachment to your email address.

I do have a formula for the date format and it seems to work for what I want it to do:

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

I would be interested in creating a de-duplication routine for my base, that you mentioned Can it be easily replicated in other bases? The key field in my base is “SCOTT #”, I use the contents in this field to check for duplicates, since the combination of numbers/letters will always be different.

Thank you!


#13

I have absolutely no idea how I missed the last two replies to this message. :wink:

I’ll try to give this a look shortly, and I’ll check my email for your CSV.


#14

Well, to start with, here’s a version of the base that flags duplicate entries as based on the value of the {SCOTT #} field.

As do all of my de-duplication bases, it requires every record in the de-duplicated table be linked to a single record in another table. In this instance, I’ve called the new table [Dupe Check], and the linked record field I’ve given the clever mnemonic {Dupe Check}.

If you enter new records manually, as part of the data entry process you will need to select the plus sign ('+') in the {Dupe Check} field and then click on the sole record in the [Dupe Check] table, which I’ve named '✅', the Large White Check Mark emoji. I chose this name because it makes it easy to see which records are not properly linked to the [Dupe Check] table, as properly linked records appear in this field as an unbroken column of check marks.

But you don’t enter records by hand, do you? In that case it’s even easier to link new records: After adding new records to the table (either through importing or by copy-and-pasting new records), go to the row immediately above the first record added. (I’m assuming it is already correctly linked, so that it’s {Dupe Check} field has a value of '✅'.) Select that cell’s fill handle — that is, the small white square that appears in the lower right corner of the cell. While holding down the mouse button, drag the fill handle to the bottom-most row of the table. This will copy '✅' into all the linked record fields of the newly added records and cause links to be created automatically.

Once records have been linked correctly, any record whose {SCOTT #} matches that of another record will have its {Dupe ?} field set to '✅'. (I’ve also set conditional colors to mark duplicate records with a red flag to the far left.) You can either scroll through the base, looking for duplicates, or set your filter to show records where {Dupe ?} is equal to '✅'. In the base as provided, there are two duplicates. (That is, there are two instances of duplication, meaning four records are tagged as duplicate.) While some implementations of these routines provide a method by which ‘acceptable’ duplicates can be indicated, it appears there are no conditions where such duplicates would be allowed, so I’ve not included that functionality, instead assuming you will simply delete all but one of the duplicate fields.

Using De-Duplication Routines in Other Bases

The de-duplication code provided here can easily be ported to other tables or bases. The only thing to watch for is to make certain you match on the entirety of your key field. For instance, you have records with the values of 729 and 729a; without precautions against matching partial values, it would be easy to indicate [incorrectly] these two records are duplicates.

To avoid this problem, I bound the key to be matched on either side with the vertical bar character ('|'). In the [Dupe check] table, when I roll up all the defined key values, I use an aggregation function of ARRAYJOIN(values,'|'), which sets the vertical bar character as the separator character; I also concatenate that aggregation function with leading and trailing vertical bars. This results in [what should be] a string [but actually is an array]¹ consisting of each key value bracketed by vertical bars, as such:

|1 CE1|135|237|247|248|249|250|251|274|

By similarly bracketing my key value prior to testing, I ensure I match only against the full field, avoiding erroneous partial matches.

I’ll take a look at the CSV import in a bit.


  1. ARRAYJOIN() is supposed to return a string — and even if it didn’t, one would think concatenating it with leading and training '|'s would cast it to a string. Instead, it appears to evaluate as an array. Accordingly, if you check the aggregation formula in {Dupe?}, you’ll see the rolled-up value addressed as values&'' rather than simply values; otherwise, the FIND() function fails.

#15

Hi

I want to thank everyone who helped with my questions. Especially to W_Vann_Hall, you made things much easier for me and the work on my base. I really appreciate your very clear and easy to understand instructions.

I will be doing an import from a CSV file to check how the dates will be formatted with the nifty formula that I got from Ptt_Pch and than proceed to the de-duplication feature, so I hope to get a better idea how it will all work together.

Thank you!


#16

Hi

I would like to send a share link to the De-duplication base that I created and perhaps it can be checked, since I think I may have missed something, since it did not seem to work.

This is the link:

I imported a CSV file and I managed to format the date field the way i wanted it, with a date formula, so that worked.

Then I set up the base for De-duplication Routines, I tried many times. I thought I followed the instructions and I was using the sample base as a guide, too, but I think I may have missed something, since it seems that it did not look right. I tested the table by copying and pasting the same records, but nothing happened.

I wanted to know what sequence of steps do I use to set-up a De-duplication base.

I am guessing that I would start with a CSV import (1st table) and than create the second table, after that I am not sure where to go next.

I noticed that the sample base has the Red color selected from the top menu, but when I tried to click on that option, it’s prompting me to upgrade to the Pro version, I use the free version at this time. Is there another way to mark duplicates or do I just use a view?

Thank you.


#17

Well, I know why it’s not working — I just don’t know why it’s not working.

Actually, the step you missed was to link every row of your [Imported Table] to the single record in [Dupe Check]. You’d do this by pasting whatever the name of that single record is into the {link to Dupe Check} field in every row of [Imported Table].

That should be all it takes to set up for deduplication; unfortunately, when I made that link, every record lit up as being a dupe. The formulas are identical for my sample base and your implementation. So what’s up?

Here is {DupeMatch} from your base:

"|
1 CE1
|
135
|
237
|
247
|
248
|
249
|
250
|
251
|
274
|
304
|
306
|
311
|
312
|
313
|
314
|
1 CE1
|
135
|
237
|
247
|
248
|
249
|
250
|
251
|
274
|
304
|
306
|
311
|
312
|
313
|
314
|"

Here it is from mine:

|1 CE1|135|237|247|248|249|250|251|274|304|306|311|312|313|314|320|325|326|327|328|329|330|353|369|372|382|413|417|418|419|420|421|422|423|424|425|426|427|428|429|429a|433|439|440|441|442|453|454|455|456|457|458|459|460|461|462|463|464|465|465a|465b|469|471|475|478|486|487|492|496|497|498|500|518|532|543|544|553|559|562|563|564|565|566|567|568|569|570|571|572|573|574|575|576|577|578|579|580|581|593|593 - (BK074c)|593a|593a - (BK074c)|610|614|617|620|621|634|635|636|637|638|639|639a|689|705|707|708|709|710|711|712|713|723|723c|724|725|733|734|740|748|749|750|751|752|753|754|755|756|756a|763|764|787|790|887|888|889|890|891|892|893|896|909|911|913|917|918|919|920|921|922|925|925|927|928|929|930|932|933|955|956|957|958|959|960|961|962|963|964|965|966|966a|980|983|984|985|986|987|988|989|990|991|992|993|1016|1017|1018|1019|1020|1021|1022|1023|1024|1025|1026|1027|1027a|1032|1033|1034|1035|1050|1051|1052|1053|1054|1055|1056|1057|1058|1059|1061|1063|1064|1065|1066|1072|1080|1081|1082|1083|1094|1099|1100|1101|1102|1103|1118|1119|1120|1121|1122|1123|1124|1125|1125a|1133|1135|1136|1137|1138|1139|1140|1163|1166|1169|1184|1185|1189|1190|1191|1192|1193|1194a|1194b|1194c|1203|1206|1207|1208|1209|1210|1211|1212|1213|1237|1238|1239|1240|1241|1252|1253|1254|1255|1260|1261|1262|1263|1263a|1271|1278|1298|1299|1300|1301|1310|1316|1321|1322|1323|1324|1325|1338|1345|1346|1347|1348|1356|1359|1362|1388|1389|1394|1395|1396|1399|1400|1401|1402|1403|1404|1405|1406|1407|1407a|1408|1409|1410|1411|1412|1414|1415|1416|1417|1418|1419|1420|1421|1422|1423|1424|1425|1426|1427|1428|1429|1430|1431|1431a|1448|1449|1450|1451|1451a|1467|1468|1469|1470|1471|1472|1473|1474|1475|1476|1477|1478|1479|1480|1481|1482|1483|1484|1485|1486|1487|1488|1489|1491|1492|1493|1494|1503|1504|1505|1506|1506a|1510|1516|1523|1523a|1523b|1523c|1523d|1523e|1524|1524a|1524b|1524c|1524d|1524e|1524f|1524g|1524h|1524i|1524j|1524k|1524l|1525|1526|1537|1538|1539|1540|1540a|1541|1542|1543|1544|1544a|1545|1546|1558|1559|1559a|1559b|1559c|1560|1560a|1560b|1560c|1560d|1561|1561a|1561b|1561c|1570|1571|1572|1573|1574|1575|1576|1577|1578|1584|1602|1607|1607a|1613|1615|1615a|1615b|1615c|1615d|1615e|1618|1619|1620|1621|1630a|1631|1632|1633|1634|1635|1638|1639|1640|1646i|1657|1672|1682|1683|1687|1688|1689|1689b|1690|1692|1693|1695|1698|1700|1708|1708a|1710|1711|1712|1713|1722|1735|1736|1737|1737b|1738|1743|1744|1745|1746|1747|1748|1749|1749a - BK209|1750|1751|1752|1753|1754|1756|1767|1768|1770|1771|1772|1773|1778|1779|1779a|1787|1788|1789|1790|1791|1792|1793|1794|1800|1810|1812i|1813i|1814i|1814M|1818|1818a|1818b|1818c|1818d|1819|1819a|1819b|1819c|1819d|1820|1820a|1820b|1820c|1820d|1821|1821a|1821b|1821c|1821d|1822|1822a|1822b|1822c|1822d|1823|1823a|1823b|1823c|1823d|1824|1824a|1824b|1824c|1824d|1825|1825a|1825b|1825c|1825d|1826|1826a|1826b|1826c|1826d|1827|1827a|1827b|1827c|1827d|1828|1828a|1828b|1828c|1828d|1829|1829a|1829b|1829c|1829d|1830|1830a|1830b|1830c|1830d|1831|1831a|1831b|1831c|1831d|1832|1832a|1832b|1832c|1832d|1833|1833a|1833b|1833c|1833d|1834|1834a|1834b|1834c|1834d|1837|1839|1840|1841|1842|1849|1850|1851|1852|1859|1860|1861|1862|1862b|1863|1884|1884i|1886|1887|1888|1889|1896|1897|1898|1899|1899a|1900|1906|1909|1909a|1910|1910a|1910b|1910c|1910d|1916|1931|1932|1934|1934i|1941|1942|1943|1944|1945|1946a|1946b|1946c|1946d|1947|1948|1949|1950|1951|1951b|1956|1961|1963|1964|1964a|1970|1971i|1973|1974|1975|1976|1977|1979|1980|1981|1982|1985|1988|1991|1999a|1999b|1999c|1999d|1999e|1999f|1999g|1999h|2002|2002|2002a|2002b|2002c|2002d|2002e|2002f|2011|2016|2019|2020|2021|2022|2023|2028|2029|2030|2031|2031a|2033|2034|2036|2037|2038|2039|2040|2041|2042|2042a|2044|2049|2050|2061|2062|2062a|2065|2066|2068|2068a|2068b|2068c|2072|2073|2076|2077|2078|2079|2080|2083a|2084|2089|2091|2091a|2091b|2095|2096|2097|2098|2098a|2099|2109|2110a|2111|2111a|2111b|2111c|2111d|2115|2118|2135|2136|2137|2138|2139|2141|2141i|2142|2146|2147|2148|2148a|2150|2151|2152|2153|2153a|2153b|2153c|2153d|2155|2157|2158|2159|2159b|2160|2163p|2164|2165|2166|2166a||

Somehow, it seems your imported table has some sort of control character flanking {SCOTT#}; if I do LEN({SCOTT#}), the seeming 3-digit values have a length of 5; 5-digit a length of 7. It could be a line-feed or newline character. Not sure where it came from.

OK, for the meantime, you can create a field called ScottFixed. Give it the formula

SUBSTITUTE({SCOTT#},'\n','')

(It appears the extra characters are newlines.) Change {DupeCheck::SupeMatch} to roll up {ScottFixed} rather than {SCOTT#}. You’ll still get 100% duplications — but that’s correct, as I see you’ve entered the values twice to force duplicates.

To return to your main point: The only thing you missed was the linking of all fields in the table to [DupeCheck]. Here’s the description from my original reply.

Hope this helps!


#18

Hi

Thank you for your reply.

I have been trying to get the Checkmarks showing (linking) and it’s still not working. I have imported the CSV file first, I than created the field ScottFixed field and copied and pasted the the formula: SUBSTITUTE({SCOTT#},’\n’,’’) into the Formula field. I added this field from the last reply:

“OK, for the meantime, you can create a field called ScottFixed. Give it the formula
SUBSTITUTE({SCOTT#},’\n’,’’)
(It appears the extra characters are newlines.) Change {DupeCheck::SupeMatch} to roll up {ScottFixed} rather than {SCOTT#}. You’ll still get 100% duplications — but that’s correct, as I see you’ve entered the values twice to force duplicates.”

Then I created the second table SupeMatch. I then proceeded to link both tables, I created the Dupe? field as Rollup: SupeMatch (link), DupeMatch (Rollup) then I copied and pasted the formula: IF(FIND(’|’&{SCOTT#}&’|’,values&’’,FIND(’|’&{SCOTT#}&’|’,values&’’)+1)>0,‘:white_check_mark:’) into the Rollup dialogue box.

but the SupeMatch field shows, in table 1, at the last field, but with no Checkmark, nor checkmarks in Dupe? field.

I than proceeded to go to table 2, which I set the three fields up already: Name-text, Imported table-link, DupeMatch-Rollup (Imported table, ScottFixed, formula: ‘|’&ARRAYJOIN(values,’|’)&’|’ into the Rollup dialogue box.

but all I got were two vertical lines in the 3rd field, (DupeMatch) so I don’t know why it’s not working. Could it be the set-up sequence?

I also was not sure about what table to select when setting up the links i.e. Name of linked field in table 1-Importrd table, select-Imported table, Table 2-name of linked field-DupeMatch selected-DupeMatch.

You also mentioned about the length of the Scott#/ScottFixed field, could I do something to correct it?:
“Somehow, it seems your imported table has some sort of control character flanking {SCOTT#}; if I do LEN({SCOTT#}), the seeming 3-digit values have a length of 5; 5-digit a length of 7. It could be a line-feed or newline character. Not sure where it came from.”

Now, if I can get those checkmarks showing I will be on my way, since I now understand how this table works, I am also a bit new working with Airtable, eventhough I enjoy learning new things and linking and rollups are new features for me. :smile:

PS I have since learned, after writing this initial reply, that I need to link individual records from one table to another, so do I first select the Dupe Check field, in the 1st, table, then select the option “Link to record in the Imported table” link, or do I go to the 2nd. table and select Imported table Field and select “Link to a record in the Dupe Check table”? I’m not quite sure.

I have attached screenshots, illustrating the above paragraph:

Thank you.


#19

I’m not an expert on this particular problem, I’m just following it because I’m interested in the method and, a little bit curious too :yum:

I think, for this to work, @W_Vann_Hall gave you the answer :wink:

Creating a Linked to record type of field when you want to link 2 tables is not enough :wink:
You need to tell to Airtable what data (what record) needs to be linked between table 1 and table 2, otherwise, the Linked to record field stays empty, as it links « nothing ».

This means that you need to tell Airtable to link a record from table 1 to another record from table 2 .

Metaphorically speaking, you can see the Linked to record field as a bridge between tables and the active linking of the records as you, actually saying what cars can use this bridge.

As I know, linking « things » in Airtable can be a bit confusing at the beginning, you could try to link one of your stamp to « where it needs to be linked » using the expanded view (instead of the grid view) of the record :wink:.
I’m just saying that because I think it’s a little bit clearer in the expanded view than the grid view « at the beginning » and it helps to understand how linking record works :wink:
(Maybe you already tried, I don’t know :wink: … It’s just an advice :wink: )

Going through the base you shared above, in the expanded view of one of your record in the [Imported] table, you find this

And I think that this is where the magic happens :wink:
(I don’t know if the base you shared with us above is still up to date though)

But I’ll repeat myself, I’m really not an expert on this method as I never tried it :wink:.
So this may be just a stupid but plainly assumed comment :wink:


#20

Hi

I have an update from my previous reply.

I was checking the base that was created for De-duplicating Routines and I was comparing it to the base that I created and I noticed that I did link the two bases and all the records, but for some reason the checkmark is not showing in the 1st field of the second table (Dupe Check table), but it does show for all the records in the Dupe? field in the first table.

I took screenshots of the same, but from different databases. The first screenshot is my base and the second screenshot was the original base, so I don’t know how to correct this. In my previous reply, #18, i wrote the sequence setup that I used.

Thank you.