Sql type queries features

[MICHAEL_RINALDI]

I’m absolutely brand new to this platform. I have a largish Excel spreadsheet of donations by donor versus time. Does your software allow queries (similar SQL) of these data?

Thanks, Mike

[Jeremy_Oglesby]

Airtable does allow that, yes.

In Airtable, a query is made by fashioning a “View”. All of your records will be in table form by default (looking much like your spreadsheet, but ultimately behaving very differently):

example

That view doesn’t really have any “query” language filtering it, except to hide some fields (columns) that I don’t need to see.

But here’s the power of Airtable - making a query is as simple as fashioning a “View” using filtering, grouping, and sorting to show me exactly the data I want to see I that view, and organized in the way I want to see it:

now I’m viewing all the same records, but grouped first by my “Assessment” field, then by my “Category” field, so that similar records are grouped together

here’s the grouping menu for the view you see above

I can add a filter to show me only records for the “2018 - ADOT (Phoenix)” Assessmente

So in that “View” in the last screenshot there, I am essentially making a query something like:
(I’m a novice at SQL, so please excuse any syntax mistakes)

SELECT Method, Demod_By, Notes, etc.. FROM Method-Demos 
WHERE Assessment LIKE '2018 - ADOT' AND Location = 'Phoenix' 
GROUP BY Assessment, Method-Category
ORDER BY ...

So a view is essentially just a saved query built by using a graphical interface.

Things like data aggregation can be done either with formulas in new fields (which can be hidden/showed in a View) or also in Join Tables (just creating a new table that links to records from 2 or more other tables, pulls in the data from them, and then aggregates it as needed). So aggregations and joins are not done directly in the View query, but rather done by building new tables or formula fields.

Airtable is, first and foremost, a database, and it behaves mostly as you’d expect a SQL system to behave, only with a graphical interface.

Hope that helps

[Joshua_Owen]

Is there a way to do union queries on airtable

[Jeremy_Oglesby]

Not technically… I think the closest you get is using a filter with OR logic.

But if you have all the data present from two tables inside of one (which is done using linked records, lookups, and rollups), then there’s no reason you couldn’t make a filter using OR logic to filter on the value of a native field, OR the value of a lookup field from another table.

What exactly are you trying to get that would normally require a UNION query?

[Akihiro_Kitami]

I have the same problem/wish. I think you need to understand how an union query works first. What I want to do and probably what Joshua wants to do is to link a table vertically. I want to combine tables containing the same columns but different data. The lookup function will only let you add horizontally by increasing columns and there will be no reference to pull up the information because they are separate sets of records (table A may not contain lookup values in table B).
Having different tables with same columns may not make sense to some but I want this so I can avoid getting the table too large and will also be easier to purge.

[Vic_Dorfman]

I ran into this issue when trying to create a central accounting table for my business whilst pulling in payment data from several different sources:

1 - Paypal Account #1
2 - Paypal Account #2
3 - Stripe Account
4 - Schwab bank account

Naturally, they all have different CSV formats. Some of the values also have different formats. Paypal #1 spits out the date in MM-DD-YYYY whilst Paypal #2 spits out date in DD-MM-YYYY.

The way I got around this problem is by placing all distinct data into its own “source” field, manipulating it using a “middleman” field (when necessary), and then using a FUNCTION field with IF / THEN logic to pull data from the “source” or “middleman” fields to display what I need.

Crucially, when I’m finished I hide the “source” fields and “middleman” fields so I’m left with a neatly combined data set.

Let me illustrate using the problem of differing DATE formats from my two Paypal accounts:

cl.ly/abb3b3354fe8/Image%2525202019-10-19%252520at%2525209.45.36%252520PM.png

*I can’t add images to my posts yet since I’m new to the community so please follow the link above to see a screenshot of the base.

A) In the Raw Date (US Paypal) field I’m simply importing directly from the CSV that Paypal spits out.

B) Next, I extract the date as a string in the Date String (US Paypal) field using this formula:

DATETIME_PARSE({Raw Date US Paypal}, 'MM-DD-YYYY')

*For this dates scenario, this “middleman” field turned out to be unnecessary because the destination field can parse the date directly within the IF/THEN formula. But it’s still a useful concept to know so I’m leaving it on for thoroughness.

C) I rinse and repeat the process for my other accounts, which all have their own date “source” field labeled accordingly, e.g. “Date (Stripe)”, “Date (Schwab)”, etc., and their own “middleman” field also labeled accordingly.

D) In the Primary field of this table—plain ol’ Date—I use another formula which parses the date from the “source” date fields if they are NOT empty:

IF(
{Raw Date HK Paypal}!="", DATETIME_PARSE({Raw Date HK Paypal}, 'DD-MM-YYYY'), 
IF(
	{Raw Date Stripe}!="", DATETIME_PARSE({Raw Date Stripe}, 'YYYY-MM-DD'), 
IF(
	{Raw Date US Paypal}!="", DATETIME_PARSE({Raw Date US Paypal}, 'MM-DD-YYYY'),
	IF(
     	{Raw Date SCHWAB}!="", DATETIME_PARSE({Raw Date SCHWAB}, 'MM-DD-YYYY')
			))))

E) I then set the output format for the Date field to be in the format “September 21, 2019”.

Now, regardless of the source of a particular row’s data—whether it’s from Paypal or Shmaypal or Stripe or Shmipe, it gets parsed and combined with all of the other data.

I’m left with a useful whole that I can now roll up into “Vital Stats” that give us a quick pulse check on our important accounting figures (e.g. Revenue, Expenses, Profit, etc.) on a monthly, quarterly and yearly timeline.

F) Worth noting:

I also created views for each of the accounts (e.g. US Paypal) whose fields are organized in the exact sequence and format in which the CSV occurs. So all I have to do is import the CSV once a month, copy / paste the whole shebang, and place it in that view.

This dates example is just a way to illustrate this solution but hopefully you can see how you can use this approach for all sorts of situations.

I’m hoping I understand correctly that this is essentially a workaround way of running a union query, but I’m not well versed in SQL so I may have answered a totally different question!

In any case, I hope it’s useful to somebody. :slightly_smiling_face:

[Justin_Barrett · Accepted solution]

Thanks for sharing your tips, @Vic_Dorfman! :grinning_face_with_big_eyes: Here’s a quick tip of my own based on the formula snippet you included. While comparing a field against an empty string is probably the most intuitive way of checking for an empty field (especially if you’re coming from a programming background of any kind), Airtable also allows a slightly simpler method: just put the field name on its own. Here’s the first part of your formula with that change:

IF({Raw Date HK Paypal}, DATETIME_PARSE({Raw Date HK Paypal}, 'DD-MM-YYYY'),
...

Logically, that equates to “If this field contains anything, do this stuff.” The benefit of this method is that it works for all field types: text, numbers, dates, checkboxes, etc.

[Vic_Dorfman]

In the immutable words of Butthead, “that was cool!” Thanks for the slick tip @Justin_Barrett! :slightly_smiling_face: