iOS Shortcut to SQL

Hi

Is it possible to save information from a shortcut or Scriptable to an SQL database?

Personally I don’t think it’s possible but just want to ask.

It depends on how you can access this sql database. If it’s accessible via a url then there’s nothing stopping you in shortcuts or scriptable from accessing it.

Can that be done?

I thought you could only do it using php.

PHP is not the only way to access an SQL database. But you’re going to have to tell us a lot more…

… What database software manages the database you want to access using SQL? Where is it running?

I suspect you either mean:

  • MySQL or MariaDB

Or:

  • You have a vague notion SQL is a good way to store and access data.

If the latter then maybe something like DataJar is a better fit for your actual need.

Let’s explore this a little.

And if not Data Jar, then Airtable is probably a good bet. It does create documentation for each base which if you’re not experienced with databases is very helpful! (And is helpful for those of us experienced with databases who have a severe case of “urgh, too much work!” :laughing:)

1 Like

Right. I guess it depends on whether you want “on device” or “in the cloud”.

Which is another reason to seek clarity on @Danjuan’s actual need.

Ok

Apologies for not giving too much information, I’m trying to get this organised in my head.

Basically I am wanting to create a way of adding new clients to an arrange of products.

Clients come in from all angles such as social media, web, phone, email etc so I want to try and automate that.

Every time I get a new client, I manually create a folder in Google Drive to save hard disk space but I’m happy enough to swap to iCloud or Dropbox if needed.

I then manually fill the clients details into QuickBooks accounting software.

I then fill in my airtable database which I intend to use as a crm.

(QuickBooks and Airtable have API’s)

Trying to get all this to work is the problem!

1: I want to be able to say ‘Siri, add new client’ and a html form appear which I will fill out. It will then create the folder, add the record to QuickBooks and add the record to Airtable.

That part is pretty much done
Using a scriptable script. I’ve just not added the QuickBooks part yet and I need a way of having the form show full screen and without an address bar.

I would like to use pretty much the same html form on my website and when a customer fills out their details, it will do the exact same.

The thing is, it doesn’t seem possible to connect an HTML form to Airtable, I’ve searched and posted on their community with no answer. So my thought was, how about I ditch Airtable and just use an sql database. I’m not using any of the airtable functions as such, I’m just using it to store details very much like an sql database.

I also don’t think it’s possible to create a folder on iCloud remotely (I.e from an HTML form)

I have connected a scriptable script to Airtable and that also creates a folder in my iCloud Drive but I’m now lost

Help me please lol

Is there anything blocking you from using Airtable forms? It seems like it’s what you’re looking for.

Otherwise, it is possible to connect an HTML form to Airtable using javascript. It’s just not trivial.

I can’t work out how to connect to it, I thought the best place to ask was the Airtable community and I’ve seen the same question in the past and no answer. I can only think that it’s not possible.

It is possible. It’s just not simple. It requires using javascript to gather the form field values and then POSTing to the Airtable API.

But, once again can you use the Airtable forms instead? It would be a lot easier.

If you want to stick to an HTML form you create, you’ll need to refer to the API docs for your particular table. Specifically the create a record section. You could use the JS SDK if that’s easier.

With which ever route you go, raw fetch calls or the library, you’ll also need to retrieve the values from the inputs you create in your form.

Pseudocode

Something along these lines

	<form id="submit">
		<input type="text" id="name" />
<button type="submit">Submit</button>
</form>
<script type="text/javascript">
function submit(event) {
    event.preventDefault()
    let name = document.getElementById("name").value
    let records = { records: [{ fields: [
        Name: name
    ]}]}
    fetch("https://api.airtable.com/v0/" + <your table data>, { "method": "POST", body: records, headers: {
        "Content-Type": "application/json",
        "Authentication": "Bearer <your api key>"
    }
}).then(res => res.json())
}
let form = document.getElementById("submit")
form.addEventListener("submit", submit)
</script>

Hi

I’d rather not use their forms, I can’t remember why bit there was a reason why I wanted to use my own.

Thank you for the script, I added my api and table but unfortunate is doesn’t work, possibly error with Name: Name?

If that works, would this be usable on a website? Surely the API key would be seen?

Ah yeah that was not supposed to work. Just roughly what the code would look like.

But yes your api key would be exposed doing it this way. I don’t really know of another way to do it :pensive:

Thank you.

I thought something like this would be simple but searching all night, I cannot find an answer.

If you are using MySQL, SQL Server, or PostgreSQL then you can use the excellent apps by the developer Filippo Maguolo. He has apps for each of these servers (as well as SQlite) and they include actions that permit you to issue SQL to the servers.

Using PHP or another programming language you could write a web service that is accessible over HTTP that returns the results in JSON as per this example in the Apple Support page Handling dictionaries using the Get Dictionary Value action in Shortcuts response.

You would have to be careful with the security of the script deny access to someone who was not authorized to do so.

If anyone is really interested in doing this, let me know and I’ll cook up some sample code.