FM & JSON pointers

[Zab37]

Hi everyone. It's been a long time since I got here, I even lost my old ID (couldn't find the email I used;-) . Wow This forums as evolved big time. 

I need this great community once again.  I really need some pointers from experiences JSON users.  Because I'm totally new there. Here's what I need to do. 

I have created a FM db with a PriceList imported from Zoho Books/Items.  I want to update the latest prices from Zoho Books/items.  I'm not sure which way to go and what to learn first. Because there will be more of this type of integration between Zoho One and our FM databases.  From my first researches I see a lot pf people using Postman, I played a bit with it and I think I got the basics. Still I'm not sure if I need to use it. 

Those are the different ways I see it.  

1. I import (or it's done automatically) the price from one item from Zoho one to FM using JSON  -  Problem is, the Zoho support seems quite poor. I can't find much help there.

2. I make a script directly into FM to update, once a day,  all records with JSON ? Need to learn how. An example would be nice.

3. I do it with Postman? but how? I don't want to waste time learning this now if it's not needed.

I haven't find the right way to update the OAuth , it looks like I need to do it every day. 

So I'm lost a bit, if anyone can give me some pointers or formation to what is the right way to do this and where to start, and maybe a good video/formation, I'd appreciate it. 

I keep looking. 

Thanks a lot

Isabelle

[bcooney]

May I suggest Chris’ api tutorials. http://filemakerandapis.com

postman is used to quickly work with api endpoints. Often, you explore an api to see what data is returned and what data is required when you Post. Postman let’s you do this without first scripting anything in FileMaker. 

[comment]

I can't figure out what your question is. In general, there are two parts to consuming data from an API:

1. interfacing with the API to get a response;
2. ingesting the response.

I know nothing about the Zoho API so I cannot say anything about the first part. Regarding the second part, you speak of "import ... using JSON". There is no such thing. Filemaker cannot import JSON. You need to parse the JSON and create (or update) each record individually yourself. OTOH, Filemaker can import XML directly (with the help of a suitable XSLT stylesheet to transform it into Filemaker 's XML grammar). So I would suggest you start by finding out if the API is capable of providing an XML response.

If not, and you need help with ingesting the JSON response, then post an example of such response along with a description of which parts you need to go where. 

 

Edited March 17, 2022 by comment

[Zab37]

The thing is yes I am confused. I'm new to this API, REST JSON and POSTMAN world. Put simply

Zoho Books is online accounting system. I know it can connect with many APIs but it's not automatically setup for FM.  It's still possible to do it. 

So I extracted an excel files of my items in Books, started a new Filemaker DB with it, using only the important fields, id, sku, barcode, item_name, category and purchase_price. 

So far so good.  

Now I'm looking for the right steps to keep my prices up to date in FM (to include them in a Bill Of Materials).

Is it best to script the JSON in FM, Get everything and parse, loop, update this way? 

In Books, I have my user, my secret question, I can generate my Oath with Postman, But I have to do it everyday. Maybe I only need an example on how to regenerate automatically my token, and a script to do it in FM so I can GET and update those prices easily on demand, from a button. 

I'm sure it's simple , I just never use JSON. 

Logically it seems it should be done from Books, if the price is updated, BAM  it's posted in the same item in FM. But I can't get help from them. 

I hope it's clearer and I used the right words this time. 😉 

Thanks again. 

I will eventually learn how to do this in my sleep, but I just need a quick fix to finish that big project.  

 

[bcooney]

Check out the tutorial I linked to. I think it’ll help you picture the setup. Also, an item update in zoho might trigger a webhook which you can receive. Otherwise, you will poll periodically for modified records based on a stored “marker” date you have in FileMaker. 

[Zab37]

7 hours ago, bcooney said:

Check out the tutorial I linked to. I think it’ll help you picture the setup. Also, an item update in zoho might trigger a webhook which you can receive. Otherwise, you will poll periodically for modified records based on a stored “marker” date you have in FileMaker. 

Thank you that's what I was looking for.  A file with examples to wrap my mind around.  

Then I guess I'll do it all from Filemaker.  I have already connected to Books with Postman and it works great. So I'll just Get everything and parse it.  

I'll keep the "webhook' notion in mind in case they answer me back. I think it would be the best to update one price at a time instead of getting and parsing thousands everyday, it seems like a waste of energy and bad practice. but hey... one day.

Or I'll try to find a way to get only those that were modified today...

Thanks a lot bcooney

[bcooney]

Yes, only fetch modified since the last Get. 

For me, api integrations all follow the same pattern. We’ve created some tools to help handle the payloads. See Generator. https://www.geistinteractive.com/products/generator/

we also process payloads using transactional scripts that receive the payload json and parse it, and set it in fields. If there is an error, the entire process can be reverted. That is especially useful when it matters. You wouldn’t want to GET an invoice and its line items, but only process some of the line items successfully. 

I did a webinar explaining transactional scripting recently. You can view it here: https://youtu.be/EvsNTkTUvpg

in fact, once you have the transaction scripts setup, FileMaker APIs to us, you’re quite empowered. Now you’re context free!  just send that script a payload, and your data processing is guaranteed. With our product Otto, https://www.ottofms.com , you can catch a webhook and send the payload to a receiver file.  we are about to release a free file that you’d host to receive payloads and queue them for processing  that file will in turn call the controller.

it’s all about the tools for us. Makes you so much more productive.   In fact, you’re  free to base your integration on our LedgerLink product  which is a file to sync with QBO. might be some approaches in there that you can reuse.

 

 

[Zab37]

Hello Again and thanks Barbara for the links.

I’m stuck again, and need your kind help.

I’m advancing, I setup everything on Zoho, I asked for my token, manage a script in FM that refresh the token with a -X POST.   Now I want to GET the list of items and nothing works from FM but it works just fine from Postman. I checked and re-checked the link, url. My feeling is there is something I do wrong with the  cURL, a syntax problem maybe ? 

Thank you.

 

I’m sending some screenshot in the hope that you can see the error.

[Zab37]

 

On 3/18/2022 at 2:05 PM, bcooney said:

For me, api integrations all follow the same pattern. We’ve created some tools to help handle the payloads. See Generator. https://www.geistinteractive.com/products/generator/

Hello 

I finally got time to test the Geist Generator and it is simply awesome (I'm having lot of fun ;-). Thank you for this link. I am getting quite good at integrating this.  I have a question. My list is around 800 items and I could simply update those that were changed since the last request date & time. In my imported file there is this field: "last_modified_time" : "2021-11-25T15:03:38-0500",  as explained here in the response : https://www.zoho.com/books/api/v3/#response

But I can't find a way to access it within my param. Do you have any idea if it's possible ? even if it's not part of the API items elements here:  https://www.zoho.com/books/api/v3/#Items

if not I will Get everything and script it to find everything greater than the last update. 

Thank you. 

 

[bcooney]

I’d contact their tech support. Typically, the get items would support a query parameter that would allow you to specify the date limit.