Airtable Database Publishing Stack

I’m close to having the MySQL stack ready for testing. It’s very much like the Airtable stack, with support for the intro, record list, outro, and no records drop zones - and of course, support for token substitutions. The big difference is that you have to provide a SQL query to specify the record(s) that you want to retrieve / publish. The nice thing about that, is that you can join to multiple tables, easily sort the records, use functions, and so on.

Below are a couple of screen shots showing how the stack works. In the example, I’m using @joeworkman’s Warehouse Image stack to display the product photos.

I’m hoping to release the MySQL stack before the end of the month, along with the FileMaker stack. That might be a little ambitious, but we’ll see.

~ Tim

1 Like

Thank You Tim. Yes, That does help, I was trying to grid things and forget about float options.
Let me see what I can work out.

As for Filemaker, and as much as I have salivated for a Filemaker connection to RW over the years, I now think it would be great for local development, but less useful for real production use.

A Filemaker solution would require a FM host, which is not that common, then a copy of
Filemaker server, which has no upgrade path as of late, so that means buying every new version for every upgrade, and quite frankly FM is just slow. Great for local dev and data gathering organiztion and front-end schema, but less great for real world usage. Not to mention a ODBC connector, limited FM to SQL interaction, etc. So, as much as I still would like to play around with it, I’m a little conflicted on how I would use it. The Filemaker roadmap has been tragic over the years. FM should have had a free reader like Adobe Acrobat to drive adoption, and a paid version to do the dev, and should have got on-board with a direct SQL backend ability with no hassles. FM has been disappointing.

So, excited to see the MySQL version. It’ll just be easier to implement as all Apache hosts will have and MySQL options available and are included for free in most hosting plans.

Thank You for all your help, can’t wait to run the SQL version.

1 Like

Hello Tim and thank-you for your Air Publisher Stack.

I’m trying to use long text Airtable fields with format (I think it is markdown) but it doesn’t display in the Rapidweaver site.
Do ou have any advice ?
Here is an example: https://res.tpc.ch/faq-mobile/page/


Thanks for your help.
Thierry

Hi Thierry,

Welcome to the forum. Have you purchased Stacks and entered the serial number? I only ask because I can see the “Buy Stacks” ribbon in your screenshot and wonder if Stacks is still in demo mode, so limited functionality that would stop the Air Table stack working?

Hi Thierry.

Great question!

You’re correct - the Airtable API returns formatted long text fields using Markdown, which is a nice feature of the API. However, because of how the Air Publisher stack works, using a Markdown stack to convert the markdown returned by the API to HTML is problematic. (Specifically, the issue has to with when Air Publisher replaces tokens.)

I do have a solution for this, but it’s a technique that isn’t as straightforward as I’d like it to be. Essentially, it involves doing the “markdown to HTML conversion” on the client-side (using Javascript) instead of server-side (using PHP).

Here’s a screenshot showing some formatted text sourced from an Airtable base.

At some point, I’ll write up a blog post and provide a sample RapidWeaver project that demonstrates this technique. In the meantime, here are some notes on how the technique works.

First, in RapidWeaver, pull up the page that you want to display the formatted text on. Then, in the Inspector, select HTML, and click on the Head tab. Then paste in this code:

<!-- Source: https://marked.js.org/ -->
<script src="https://cdn.jsdelivr.net/npm/marked/marked.min.js"></script>

<!-- Source: https://locutus.io/php/strings/nl2br/ -->
<script>
	function nl2br (str, is_xhtml) {
		if (typeof str === 'undefined' || str === null) {
			return '';
		}
		var breakTag = (is_xhtml || typeof is_xhtml === 'undefined') ? '<br />' : '<br>';
		return (str + '').replace(/([^>\r\n]?)(\r\n|\n\r|\r|\n)/g, '$1' + breakTag + '$2');
	} 
</script>

Next, in the Record List Stacks area of the Air Publisher stack, where you want the formatted long text field to be displayed, use this code:

<script>document.write ( nl2br( marked(`{{Formatted}}`) ) );</script>

Be sure to update “{{Formatted}}” token with the name of your field. Also, be sure to retain the backticks (`) that wrap the token.

So there are two Javascript functions involved. One converts the markdown to HTML, and the other retains any line breaks in the field.

Here’s another screenshot. This shows the project’s stacks, etc.

Then preview your page, and you should see the field values displayed properly (with the correct formatting).

Note that this technique also works with Airtable fields in which you’ve directly entered Markdown. In other words, you can enter Markdown directly in long text fields, and then have it display properly on your site. So potentially, you could use Airtable, the Air Publisher stack, and Markdown, as a lightweight CMS.

If you have any questions about this technique, please let me know. Also, I’ll be sure to post back to this thread when I have a blog post and sample project available.

Thanks,

Tim

2 Likes

Hello Tim and thank-you for your quick answer.
The result is much better: https://res.tpc.ch/faq-mobile/
I just see a remining problem: the spaces are too big between lines. Where can I change this ?
Thanks again and have a safe day.
Thierry

You might be able to do something like this:

<div style="line-height: normal;"><script>document.write ( nl2br( marked( `{{Formatted}}` ) ) );</script></div>

~ Tim

We tried but had a problem to get the licence Number mail. My colleague is in contact with Realmac.
Hop it will work asap.
Regards

Ah, Realmac make Rapidweaver but the Stacks plugin is not. Stacks is made by Isaiah at Yourhead software. @isaiah would be the best person to help with licence issues

:pray:t2: Thank you Tim, it is working now. Have a nice day.

Hi
Do you have a contact by Realmac Software, We have bought the licence but never received the licence number by mail.
We will purchase Stacks as soon as we have RapidWeaver.
Regards
Thierry

Bottom of this page there’s a license retrieval, and if that doesn’t work right below that is a contact email link.

I can’t get Airtable/Airpublisher to work. API Key, Base ID and table name are set correctly (I’m really sure). However, the debug info shows this errors:

’ . PHP_EOL; echo 'Debug Information
’ . PHP_EOL; echo '• Error Type: ’ . $response[‘error’][‘type’] . ’
’ . PHP_EOL; echo '• Error Message: ’ . $response[‘error’][‘message’] . ’
’ . PHP_EOL; echo '• API URL: ’ . $url . ’
’ . PHP_EOL; echo ’

I’m on RW 8.6.2, Foundation 1

Accessing the table via https://api.airtable.com/v0 is no problem

First, make sure that the page’s filename extension is “php.”

If that isn’t the problem, then it could be a conflict with another stack. To troubleshoot this, try to remove or disable any stacks (especially those that use PHP) in the Intro, Record List, and Outro drop zones.

I hope this helps.

~ Tim

Thanks for your reply. Extension was php.

I noticed that I had to put a stack into Record List zone in order to show records.

However, the error message still remains in preview mode, and the option"Fields returned by the API" does not work for me.

I’ve just posted a new version of the Air Publisher stack (v1.1). It resolves issues that occur when the Airtable API returns only a single record. This might resolve the issues that you’re running into.

When you get a chance, please give it a try.

You can download it here:
https://timdietrich.me/stacks/air-publisher/downloads/

Thanks.

~ Tim

1 Like

Hi Tim,

Love the stack - I know you must have put a lot of hard work into it and it’s really generous making it freely available.

Would you consider doing a modification for a reasonable license fee?

It’s really awkward trying to get the records presented in a grid format, using other stacks (Like Foundry ‘card’ stacks etc)… but I think if the sections of the Air Publisher stack were split into separate stacks,
ie:

  1. Intro Stack
  2. Records Stack
  3. Outro Stack
  4. No Records Stack,

Then the database connection can be configured separately to the returned records, which could contain a number of stacks, and likewise embed any number of formatted/nested stacks, and the ‘records stack’ itself could also be formatted separately…

eg: on the page where the records need to go, assuming the other stacks have been configured:

Psuedo Code:

    <<Foundry-Card-Deck Stack>>
      <<Air Publisher Records Stack>>
         <<Foundry-Card Stack>>
         <<Foundry-Card Stack-Title>> (Contains Token from Air Publisher)
         <<Foundry-Card Stack-Paragraph>> (Contains Token from Air Publisher)
         <<Foundry-Card Stack-Image>> (Contains Token from Air Publisher)
       <</Air Publisher Records Stack>>
    <</Foundry-Card Records Stack>>

So in the above example, the Foundry Card Deck stack is a container for any number of individual card stacks… It’s simple enough to currently use multiple stacks in each row of returned records… But it’s impossible for them to be nested in the record loop, unless the record loop was a stack of its own…

I have tried using a HTML stack in the intro and outro, but despite Rapidweaver settings being configured NOT to autocomplete HTML, it still keeps adding </div> tags and breaking the code in my intro html stack, and it makes it near impossible to edit the stack the HTML came from.

Hope that makes sense.

Jason Sheldon

Jason,

It’s interesting that you’ve made this suggestion, because the multi-stack approach was what I originally used to develop the stack. I decided to use the single-stack approach because it seemed easier for users to implement the stack. But you’re correct in that it does make it difficult - or impossible - to use the stack depending on what you’re trying to do with it.

I am using a multi-stack approach on the new FileMaker stack (and it’s one of the reasons that I’ve delayed releasing that stack). There are separate stacks for defining a database connection, finding records, getting a specific record, and so on.

If that works out, then I’ll release a multi-stack version of Air Publisher - and likely use the same approach for the other database publishing stacks that I’m working on (for MySQL, SQL Server, NetSuite, and so on).

1 Like

Woohoo! This is great news!

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.