Exporting/Printing Only Non-Empty Data


#1

Hello,

I am new to Airtable, so please bear with me if i’m asking a question with an obvious answer.

I am collecting Questionaire information via a form… Many of the answers to the questions will exist as null-value fields (eg. a ‘Yes’/‘No’ question where the answer is ‘No’). I do not care about ‘No’ answers… They are irrelevant once the respondent answers ‘No’

I want to devise a way to export only the non-empty data (the ‘Yes’ answers). I see that in the Kanban view when I ‘Customize Cards’ and ‘Show All’, what shows on the Kanban card is only the non-empty data… this is great! However, when I try to export, i cannot generate a printable view that only shows me the non-empty information… this leads to a lot of noise.

Am I missing something? Is there an easy way to accomplish what i’m looking to do? any advice would be greatly appreciated


#2

The following, albeit a little clunky, will probably get you what you need. There may be easier ways to do this, depending on your precise situation and needs; if I’m too far off the mark, let me know a bit more about your base, and I’ll try again.

This method takes advantage of the way Airtable treats text with end-of-line characters when entered or pasted into long text fields. The procedure I give here is a manual one – that is, it requires you to copy data from one column in your base and paste it into another one by hand, which is fine for occasional reporting. However, it can be automated through Zapier, and later on I’ll point you to a document that shows how.

First, the manual process. You will be creating two additional fields in your base, a formula field and a long test field. I will call the formula field {YesAnswersLF} and the long test field {Yes Answers}. As I am not sure exactly what your base holds, I am assuming it contains a series of fields with a value of ‘yes’ or ‘no’; that is, I assume the text of each question is not stored in each record. Alternatively, you may have stored values as a checked check-box for ‘yes’ and an unchecked one for ‘no’ or some similar encoding; if so, the general process remains the same, but the specifics of the ‘if’ statments may vary.

  1. Depending on how many questions you have, configuring the formula for {YesAnswersLF} could be a pain. Essentially, you want to step through your questionnaire and create a string containing each ‘yes’ answer followed by a carriage return; ‘no’ answers are ignored. You’ll want something along these lines:

    IF({Question1}='yes','Short text for Question 1'&'\n','')&
    IF({Question2}='yes','Short text for Question 2'&'\n','')&
    IF({Question3}='yes','Short text for Question 3'&'\n','')&
    IF({Question4}='yes','Short text for Question 4'&'\n','')&
    IF({Question5}='yes','Short text for Question 5'&'\n','')

    (The example is for a five-question survey, obviously; you’ll need to add a line for each question in your actual questionnaire.) What this does is builds a text string consisting of a statement for every ‘yes’ response separated by ‘\n’ — the line-feed character, which UNIX uses to indicate the end-of-line for text. Unfortunately, Airtable treats formula fields resulting in a text string as single-line text fields; if you attempt to display or print {YesAnswersLF}, you’ll end up with a run-on string where each individual ‘yes’ text is separated from the next by a single space character. To get each ‘yes’ text onto ts own line, you need to pull the value of {YesAnswersLF} into a long text field.

  2. To do just that (pull the value of {YesAnswersLF} into a long text field) do the following:

    1. Click in the {YesAnswersLF} field in Row 1 (or the uppermost row you wish to copy) of your base.
    2. Scroll to the bottom row (or the bottommost row you wish to copy) of your base.
    3. While holding down the ‘Shift’ key, click in the {YesAnswersLF} cell in this bottommost row. In the lower left of the screen, you should see a message reading '### cells selected'.
    4. Press Ctrl-C to copy the cell values. An alert box will pop up, again in the bottom left of the screen, reading '### cells copied'.
    5. Click in the {Yes Answers} field in the uppermost row — that is, in the {yes Answers} long text field in the same row where you clicked in sub-step 1, above.
    6. Press Ctrl-V to paste the values copied from {YesAnswersLF} into the matching cells of {Yes Answers}.

When Airtable encounters a line-feed (’\n’) in a long text field, it treats it as an end-of-line. Frankly, this is of limited utility: In kanban and gallery views, the first four lines are displayed[1]; multiple lines are displayed, up to the available space, when using the Page Designer Block; and — tah-dah! — when “Wrap long text onto multiple lines” is toggled on, multiple lines of a long text field are printed.

Accordingly, to print your questionnaire results with each ‘yes’ answer on an individual line and with no ‘no’ answers, do the following:

  1. Create a new Grid view.
  2. Select ‘Hide fields’. When the ‘Hidden Fields’ window opens, go to the bottom and select ‘Hide all’. This will hide all fields except the primary field, which can not be hidden.
  3. Unhide {Yes Answers}.

Now, when you print from this view, you will see something like this:
collapsed_print_example
This is a portion of a print-out from my Wardrobe Manager base in Airtable Universe. The {dimensions} field in the third column is a long text field populated by copy/paste from a line-feed-separated field similar to {YesAnswersLF}. (In the case of Wardrobe Manager, {dimensionsLF} is a rollup field using an aggregate function of ARRAYJOIN(values,'\n'), but the concept is the same.)

Obviously, if you wish to print or export fields other than the primary field and {Yes Answers}, you will need to unhide them in this view; presumably, the number of fields and the length of the text within them will affect how data is presented, but I have not played around enough to say how.

As I mentioned earlier, the copy/paste mechanism can be automated using Zapier — in fact, it can be performed using a two-step Zap and, as such, can be run from a free Zapier account.[2] If you are interested in doing so, within Wardrobe Manager there is a [documentation] table that contains user guides and related information for that base, stored as PDF attachments. One of these is the Wardrobe Manager Zapier Guide, which provides step-by-step instructions on how to configure a Zap to perform just such a copy-and-paste into a long text field. (If you wish, you can go to the base on Airtable Universe, select ‘Explore the base’, scroll down to the [documentation] table, and select and read the Zapier guide from there without having to copy the base into your own workspace.)

I have a tendency to over-explain, so this is actually a simpler process than it may look at first. Again, a lot of this is a best-guess as to your precise requirements, so if I am incredibly off-target, let me know, and I will take another whack at it.
. __________
1. Seemingly, empty lines are not hidden; if the field displays at all, a four-line chunk of screen space is used, regardless how many non-blank lines it contains. Truly empty — as in newly created — fields are suppressed, but some fields I would assume to be empty — for instance, ones created by rolling up a null set of linked records — display as four blank lines; I have yet to understand why…
2. Note that a free Zapier account is limited to 100 events — in this case, 100 questionnaire records — per month.