ContributionsMost RecentMost LikesSolutionsJotforms API Hey folks - Sharing a win for future generations - I use Jotforms on the website to gather simple form data...but their webhook payload is in multipart/form-type rather than JSON. Had to take a couple steps to make it usable. Getting the Submission ID Their API lets you call form data and get a JSON response if you know the submission ID. After resetting up the incoming webhook, fetch JSON using the following: {% set data = a.body %} {% set start = data.find('name="submissionID"') + 20 %} {% set end = data.find('-', start) %} {% set submission_id = data[start:end]|trim %} This parses through the lengthy multipart/formdata content, searching for SubmissionID, pulling the data, and trimming any extra spaces for safety. A bit of a fragile solution because it really depends on JotForm not changing their wording, but works well enough. The api URL I can call then looks like: https://api.jotform.com/submission/{{submission_id}}?apiKey= Getting the file I have a file attachment in the 12th field of the JSON; but extracting a clean name was also difficult. The JSON only gives a result like: https://www.jotform.com/uploads/blah/blah/blah/Screenshot.png That provides the path to download the file, but to strip the name out, jinja split needs to be used; however, the node the url is on is a list. I know there will only be one file - so used the following to work around the split limitation. {% set dlurl = c.raw_record.answers['12'].answer[0] %} {% set parts = dlurl.split('/') %} {% set last_part = parts[-1] %} {{ last_part|trim }} Where the [0] pulls the index 0 from the list array. Hope this helps someone in the future! ------------------------------ Malcolm McDonald ------------------------------ Re: Record created off of a form and/or Pipeline, but then no content in the fields - why? I've read through this a couple times, and am not 100% sure I know what's expected and what it's actually doing Are there two issues here? 1) When just an individual workshop is selected, those fields don't populate 2) When the whole series is selected, just the first workshop session populates? Here's a couple generic ideas.. Are the blank fields lookup fields to a related table? If so, is the recordID being set correctly in pipelines? Pipelines looping can be tricky - and I fairly often realize that I'm working on the wrong branch or have forgotten to put a for each loop after a search Good luck, fellow canuck! ------------------------------ Malcolm McDonald ------------------------------ Re: Filter Report Based on User Role Chiming into an old thread for future people searching for similar solutions. Building on Mark's posts, I have a table with a couple dozen user guides. Some user guides are for general audiences, some restricted to admins etc. Created a multi-text selection field where the options were <Role ID # > - <Role Name> eg 10 - Viewer. Then a checkbox formula field using this formula: If(Contains(ToText([Role ID]),ToText(UserRoles("ID"))),true) It works to set the report filters to where the checkbox formula is checked, but I prefer to set it at the table permission level with a custom rule on what users can view. Don't know that I would go to this kind of trouble for a big table - but for small ones, really works nicely as a quick row-level security by user role workaround. ------------------------------ Malcolm McDonald ------------------------------ Display an image attachment on homepage Hello dear QBers! Been banging my head against this for a bit... I have an identity badge app -- where each user has an image of themselves in their record. On the homepage of that app, I'd like to show them their picture, if they have one. Wrinkle: I don't want to use the single-field report approach ... it's ugly, and doesn't translate well to mobile. I've been trying to write it as a api_doquery using the current user as the filter.. but not getting anywhere Thanks as always! M ------------------------------ Malcolm McDonald ------------------------------ Re: How do you get the full URL of attached file using an API? Updating the link reference: Appendix 2: Managing Files ------------------------------ Malcolm McDonald ------------------------------ Re: Pipeline Bucket - Can it be used to accept files from API calls? Doug -- thank you for continuing to work this very much appreciated. I've engaged QB Support too .. I think we're an inch from the finish line, but stuck at the binary encoding part. The jinja filter | base64_encode can only handle string, but the image mime-type encoding (I think I'm saying that right) has all kinds of characters outside of the 128-ascii set expected. Annoying because the source image manipulation service API won't return a url I can just download the file from, and annoying because QB doesn't accept the result it without me finding another API to do the conversion step. I can see all kinds of local server-side solutions in javascript etc, but nothing I can fire off in pipelines. If I come to a solution, I'll post here -- and thank you! M ------------------------------ Malcolm McDonald ------------------------------ Re: Pipeline Bucket - Can it be used to accept files from API calls? Thanks Doug-- that does get me further -- I can now "see" the image binary in the pipeline's output - but still can't do much with it. Intention is for the file to be uploaded as a file attachment .. but that pipeline only accepts a URL. I tried it anyways, and it didn't take the binary. I'm stuck feeling like buckets might be an approach (limited documentation but it talks about handling files?). Ideally I'm not setting up a box account or something as a temporary URL hosting.. seems like too many steps in the chain. Any other thoughts?? Thanks again for your help! M ------------------------------ Malcolm McDonald ------------------------------ Pipeline Bucket - Can it be used to accept files from API calls? Hello fellow Quickbasers! I am using an image processing API to compress and resize images before uploading to Quickbase. I have _most_ of it working using a FetchJSON pipeline step .. the original image goes out, and I get the correct response back from the image processing service. The next step escapes me though -- the result is not an application/json content type, it's an image/jpg result. EG the below. HTTP/1.1 200 OK Compression-Count: 1 Image-Width: 530 Image-Height: 300 Content-Type: image/jpeg Content-Length: 46480 [binary] With just pipelines, not codepages, has anyone been able to process a result like this? The Iterate over JSON step doesn't seem to work because there's no JSON schema to reference (and it's the wrong content type) .. I'm thinkingmaybe a bucket step would work? Thanks in advance! M ------------------------------ Malcolm McDonald ------------------------------ Re: Grouping and Listing fieldnames and values in Pipelines Actual genius .. thank you Mr. Henning! And for future searchers on this topic .. I had quite a number of "illegal" characters in the field name, but the pipeline field picker showed me what to do .. so for eg - Doorlock(Brass-15) shows as {{a.doorlock_brass_15}} .. ie the jinja needs the open parenthesis as an underscore, but drops the close parenthesis entirely. ------------------------------ Malcolm McDonald ------------------------------ Grouping and Listing fieldnames and values in Pipelines I'm importing a flat file of workorders into a staging table, then looking to load it into the production tables. The file has about 24 columns of parts that could be installed, with a count. Looks a bit like the below: DOORBELL CAMERA INDOOR CAMERA OUTDOOR CAMERA TAKEOVER MODULE DOOR/WINDOW SENSOR 1 2 1 1 2 I'm not using this data for inventory .. it's purely informational. Using pipelines - is there an easy way put all those columns into a single multi-line text block, separated into a list - skipping over the blank ones? EG -- that first record would like the below or similar: Doorbell Camera: 1 | Door / Window Sensor: 2 Thanks! ------------------------------ Malcolm McDonald ------------------------------