Jump to content
mjhnson24

Filemaker Stripe API date range

Recommended Posts

mjhnson24

So we have a stripe account. I can get filemaker to get a list of charges using the insert by URL and get json data back but heres the issues. Basically what im doing is based on the JSON data thees a paid value thats true if its been [paid or false if not so if its paid then ill check if that charge is in filemaker via the id from jason since ill capture that unique record id.

 

1. If i get a list of all charges it will pull everything from day 1 i assume unless theres a cap on how many it pulls so if there have been a total of 5000 charges over the years then it will pull all 5000 of them in JSON and i would have to loop through and figure out which ones are already in our filemaker database which figuring that out is easy when looping through JSON data but it will be very time consuming.

 

The date in their system comes in JSON data as UTC code it looks like from what i researched. (like 1569615707). i would prefer only get the ones from stripe that have been added to stripe for that day and then loop through just those and see if they are paid or not and if so add them to filemaker. I dont want to have to loop through alot of records and figure out which ones have not been paid then check to see if they are already in the system or not.

Share this post


Link to post
Share on other sites
Josh Ormond

Are you using the 'List All Charges' endpoint? If yes, the documentation says the charges are listed with the newest charges first. 

There are also starting_after and ending_before parameters that you can pick up where you left off.

  • Like 1

Share this post


Link to post
Share on other sites
mjhnson24

Yes i am. If i wanted to find all charges for today, how do i write the url in the "Insert from URL" step in filemaker to do that? Not too familiar with curl so having to learn it. I tried writing a url to get specific data but it gave back an error about the url being wrong.

 

I also noticed on the "Limit" parameter in stripe it talks about default is 10 but what if i have 11 charges that came in for a specific day from customers and need to get all of them. If it only gives me 10 then that could give a problem. i know you can set limit=100 but aint that kind of programing yourself into a box by limiting it to a max of 100? Because what if you had 101 charges that day? Not that anyone would have but still is a good point i think.

 

Thanks Josh for the help your providing. My limit is filemaker, html, some css and some javascript but thats about it and i know filemaker pretty well but still not a guru like most of yall. But one day ill get there.

Edited by mjhnson24

Share this post


Link to post
Share on other sites
Josh Ormond

Yup, understood. Just getting context so I knew where to focus my attention. Give me a little bit ( I'm at work at the moment ) and I'll try and see if I can set up a test myself.

In the meantime, there are 2 ways I can see possibly handling it based on Stripe's documentation.

1. )  Send as one of the argument: 

-d created.gt=<yesterday's timestamp converted to UTC>

 

2. )  When you get your first list of 10 or 100 or whatever number you retrieve. Store that last payment object ID. Then in the next call, include the argument:

-d limit=100
-d starting_after=<the last object ID you got from the original call>

* replace everything with your value including the brackets ( '<' and '>' ).

In #2 you might have to use 'ending_before' instead of 'starting_after'. I'm not sure from reading it which way that actually goes.

  • Like 1

Share this post


Link to post
Share on other sites
Josh Ormond

One additional note, these arguments will likely not be part of the URL itself. They will need to go in the cURL options part of the script step.

Correction: it does look like you can use 'limit', 'starting_after', and 'ending_before' in the url. I guess it depends on how much you want to do the back and forth.

  • Like 1

Share this post


Link to post
Share on other sites
mjhnson24
Posted (edited)
20 hours ago, Josh Ormond said:

Yup, understood. Just getting context so I knew where to focus my attention. Give me a little bit ( I'm at work at the moment ) and I'll try and see if I can set up a test myself.

In the meantime, there are 2 ways I can see possibly handling it based on Stripe's documentation.

1. )  Send as one of the argument: 


-d created.gt=<yesterday's timestamp converted to UTC>

 

2. )  When you get your first list of 10 or 100 or whatever number you retrieve. Store that last payment object ID. Then in the next call, include the argument:


-d limit=100
-d starting_after=<the last object ID you got from the original call>

* replace everything with your value including the brackets ( '<' and '>' ).

In #2 you might have to use 'ending_before' instead of 'starting_after'. I'm not sure from reading it which way that actually goes.

Ok, I'll try those. I tried setting up some options in curl options but it didnt seem to like them. Not sure if i did it correctly or not. So i guess i should get the timestamp of 12:00 AM that morning so i can get everything greater than that UTC time?

Edited by mjhnson24

Share this post


Link to post
Share on other sites
mjhnson24

I think i got how to write the cUrl options in filemaker's Insert from url step. I wrote it below and it seem to pull data, the only issue is the limit=2 is pulling all 5 records from our account unless the secret key isn't working. Its the same key thats on their API website but i got it from our stripe account so i guess they use the same test account secret key for everyone. I wasn't separating each parameter around quotes and using the & sign for each new line. Again just need to find out why the limit param isn't working and the finding all records for that day.

 

"-u <SECRET KEY HERE>:" & "-d limit=2"

Share this post


Link to post
Share on other sites
Josh Ormond

What version of FM are you using?

Share this post


Link to post
Share on other sites
mjhnson24

My client is FM 17 but server that hosts the files is FM 15.

Share this post


Link to post
Share on other sites
mjhnson24

I even wrote "-u <SECRET KEY HERE>:" & "-d created=1569615707" which should pull one record but it pulls all 5 records and same thing for created.gt=xxxxxxx

Share this post


Link to post
Share on other sites
mjhnson24

i did this in postman on the UTC field and it worked like it should. 

created[gte]=1569615700 ( it gave me 1 record like it should)

created[gte]=1551216907 ( it gave me 3 records like it should)

Share this post


Link to post
Share on other sites
mjhnson24
2 hours ago, Josh Ormond said:

What version of FM are you using?

I did try using a new file created on my desktop thats not on server and FM 17 client new test file did the same thing. 

Share this post


Link to post
Share on other sites
Josh Ormond

I setup an account to test last night. But the test setup doesn't have any data in it for me to see what it actually does. I assume I would have to create some test transactions to get any actual data back. It may just be a syntax issue. 

Usually, I try to compile all of the cURL options into a variable, so I can see exactly what it is sending.

Share this post


Link to post
Share on other sites
mjhnson24
Posted (edited)
23 minutes ago, Josh Ormond said:

I setup an account to test last night. But the test setup doesn't have any data in it for me to see what it actually does. I assume I would have to create some test transactions to get any actual data back. It may just be a syntax issue. 

Usually, I try to compile all of the cURL options into a variable, so I can see exactly what it is sending.

i did that just now and the variable looks like below.

-u <secret key>:-d created[gte]=1551216907 (this gives me everything which is currently 5 records.)

 

if i have a space between : and -d it gives me invalid params error.

 

Yea it may be because you dont have anything in the system. I added 5 records to test with for now on ours till we are ready to go live.

 

Edited by mjhnson24

Share this post


Link to post
Share on other sites
Josh Ormond

Look at the sample in their API documentation. The syntax is very exact.

curl https://api.stripe.com/v1/charges \
    -G \
    -u sk_test_4eC39HqLyjWDarjtT1zdp7dc: \
    -d limit=3

You can also look at the code generated by Postman for the working call. At quick glance, it looks like you can also authenticate using the Bearer token in the header. So something like this should work:

-H "Authorization: Bearer <secret_key>" \
-d created.gt=1569615707

Note the use of returns. And you need to make sure your variable shows exactly like this, including the backward slash, I believe.

Share this post


Link to post
Share on other sites
mjhnson24
11 minutes ago, Josh Ormond said:

Look at the sample in their API documentation. The syntax is very exact.


curl https://api.stripe.com/v1/charges \
    -G \
    -u sk_test_4eC39HqLyjWDarjtT1zdp7dc: \
    -d limit=3

You can also look at the code generated by Postman for the working call. At quick glance, it looks like you can also authenticate using the Bearer token in the header. So something like this should work:


-H "Authorization: Bearer <secret_key>" \
-d created.gt=1569615707

Note the use of returns. And you need to make sure your variable shows exactly like this, including the backward slash, I believe.

heres what my variable looks like. Gives me a parameter missing - must provide source or customer

 

"\ -G \

-u sk_test_uqX9Nk15qBKbkgoEagxdrDb5: \

-d limit=2"

 

and this is in the url of the insert from url option in filemaker. 

https://api.stripe.com/v1/charges

Share this post


Link to post
Share on other sites
mjhnson24

Thats why i was confused and maybe doing it wrong cause i saw that in their API and tried that but it keep giving me errors and nothing worked.

Share this post


Link to post
Share on other sites
Josh Ormond

At some point last night when I was testing it, I notice in one of the return headers that it "required" the authorization to come in as a Bearer token. Which doesn't match their documentation.  If I get time tonight, I'll throw together a sample file that you can play with and see if you can get it returning the correct data.

Share this post


Link to post
Share on other sites
mjhnson24
59 minutes ago, Josh Ormond said:

At some point last night when I was testing it, I notice in one of the return headers that it "required" the authorization to come in as a Bearer token. Which doesn't match their documentation.  If I get time tonight, I'll throw together a sample file that you can play with and see if you can get it returning the correct data.

Ok awesome! Thanks Josh! Appreciate your help on this. Yea i did have to do that in postman with Bearer. Not sure how i figured that out by i was actually wandering about that cause like you said their website don't say it. Im gonna make the change like you mentioned earlier and use bearer in the insert from url step and see what it does.

 

I do know if i do it where it just passes the -u <SecretKey>: and thats it and the url is just xxxx/charges and thats it then it works well but it pulls all the data which wont work cuase if we got 5000 records and only 5 are new its gotta loop through all 5000 JSON data records to get to the 5 thats new. Also the below code worked well but pulling all records still like it was bypassing the created=xxx part. So my guess is something is changed but not sure. 

 

-u <secret key>:-d created[gte]=1551216907

Share this post


Link to post
Share on other sites
Josh Ormond

Ok. Here is a file that should work. Enter your key. Enter any arguments in a list format like I have them. Remember, curl calls are very syntax sensitive. It has to be exact. The script step, Insert from URL, handles a little of this for you. Thus the reason the "\" is not included.

I didn't get the created.gt thing to work yet, but created did.

Also keep in mind, you are not limited to only get the data you are looking for. For example, if you get all the transactions from yesterday, as you loop through the returned data, you should know when you hit a transaction that you already verified. At that point, exit the loop. No need to check stuff you already have completed and verified as paid.

Stripe.fmp12.zip

Share this post


Link to post
Share on other sites
mjhnson24
Posted (edited)
9 hours ago, Josh Ormond said:

Ok. Here is a file that should work. Enter your key. Enter any arguments in a list format like I have them. Remember, curl calls are very syntax sensitive. It has to be exact. The script step, Insert from URL, handles a little of this for you. Thus the reason the "\" is not included.

I didn't get the created.gt thing to work yet, but created did.

Also keep in mind, you are not limited to only get the data you are looking for. For example, if you get all the transactions from yesterday, as you loop through the returned data, you should know when you hit a transaction that you already verified. At that point, exit the loop. No need to check stuff you already have completed and verified as paid.

Stripe.fmp12.zip 96.59 kB · 0 downloads

Yea im gonna create a table and match some of the fields from Stripe and then my accounting guy is gonna have a layout where he can skim through the data and click a button to send it to out aAGIS accounting filemaker file. But ill take the id from stripe and enter it into the table i create so i can match it and see if it exists and not add that charge if that id exists already in my table. Thats the goal anyway.

But i just want to try to narrow down that data i get from stripe so if we ever get 5000 records then as it stands ill end up getting all 5000 charges from stripe and have to loop through all 5000 to find the ones not in our table and i dont really need to loop through alot like that. If i get a few thats in the list like if i get 10 charges and 3 are in my table already then im find with that but not 5000 or every charge in the system. Know what i mean. Just trying to narrow down the amount of data i get back. Not sure if filemaker has a limit to the amount of data you can get in JSON format like that.

Thats why i want to try and figure out how to only get transactions for that day. I just need to figure out how and what UTC code i need to start with each day so i know i can get all the data for that day or even if its from previous day and make sure the UTC code isnt 6 hours off in the system compared to my CST time. I noticed that times in the system one was the exact time as mine from the UTC code but the other code wasnt. So i gotta figure that out more. Not sure if the UTC code in stripe is their time frame (wherevere that is) or if its matched to the time stamp of wherever the customer is that made the charge.

 

Thanks again for the help and especially for taking the time out of your night to play with this and mock-up a sample file.

 

Edited by mjhnson24

Share this post


Link to post
Share on other sites
mjhnson24
10 hours ago, Josh Ormond said:

Ok. Here is a file that should work. Enter your key. Enter any arguments in a list format like I have them. Remember, curl calls are very syntax sensitive. It has to be exact. The script step, Insert from URL, handles a little of this for you. Thus the reason the "\" is not included.

I didn't get the created.gt thing to work yet, but created did.

Also keep in mind, you are not limited to only get the data you are looking for. For example, if you get all the transactions from yesterday, as you loop through the returned data, you should know when you hit a transaction that you already verified. At that point, exit the loop. No need to check stuff you already have completed and verified as paid.

Stripe.fmp12.zip 96.59 kB · 0 downloads

Think i got the gt and gte working. I added it below the limit as created[gte]=1551216907. Kinda like an bracket notation in javascript.

Share this post


Link to post
Share on other sites
mjhnson24

Now to figure out how to get data pulled for a specific day or if i run the script each morning, like if i ran it at 7am this morning then maybe pulling anything from Oct 1, 2019 @ 12:00 AM till now i think is ok hah? Maybe get the UTC version of that timestamp and do my created[gte]=xxxx? What do ya think? Think it would work? Or do you know of a better way to do it?

Share this post


Link to post
Share on other sites
mjhnson24

Looking at what you did, i think the only thing i was missing was the -X GET in the curl Options cause i tried the Authorization: xxxx and it didnt work either till this morning when i added the -X GET. Now people will know how to do other stuff with stripe and filemaker via this form. Couldn't find much on it other than people wanting to create charges.

Share this post


Link to post
Share on other sites
Josh Ormond

Yeah. It took me a bit to figure out that the request failed as a Post, and to get the syntax correct to get it to send as a Get request instead of a Post.

One of these days, I may go through and create a module that does all of the actions, but that's for another day. 

Glad you got it working. Your plan to grab the last day or two looks to be a good approach. You can also have FileMaker go grab the status of a transaction when the user loads a record in form view for that transaction, or however you do it. Instead of 'List all charges' there is another method that you can pull the details with the id of the transaction. All good options depending on what you are doing.

Share this post


Link to post
Share on other sites
mjhnson24
2 minutes ago, Josh Ormond said:

Yeah. It took me a bit to figure out that the request failed as a Post, and to get the syntax correct to get it to send as a Get request instead of a Post.

One of these days, I may go through and create a module that does all of the actions, but that's for another day. 

Glad you got it working. Your plan to grab the last day or two looks to be a good approach. You can also have FileMaker go grab the status of a transaction when the user loads a record in form view for that transaction, or however you do it. Instead of 'List all charges' there is another method that you can pull the details with the id of the transaction. All good options depending on what you are doing.

Ok great! Thanks Josh for all your help! As far as the module i'd be your first guinea pig to use it lol. But yea the documentation doesn't really help newbies like me much. I had to dig for some of the stuff i already knew and alot of the stuff on the net is about creating charges not just querying it to get the data out of stripe for another platform. 

 

Right now we are gonna just send out an email from filemaker to all the customers like we have always done but be an html email instead now and have a link that goes directly to stripe with our internal invoice id and payment amount and all they do is fill out the basic customer info and CC info and submit and thats it. Next phase we will try to add recurring charges each month if they opt-in to do so. But once they submit payment, ill get the data from stripe each morning to see if there is anything new and add it to a middle man table in FIlemaker where the accounting guy and push that data to his accounting software which is another filemaker file.

Share this post


Link to post
Share on other sites
mjhnson24

Next task is to loop through all the json data in filemaker for each charge and add the field values for each item i need to my filemaker table and go from there. So atleast will have that programmed already in filemaker then i can play with the UTC timestamp stuff. I think i found a custom function thats good that can give me atleast the date from the UTC data from stripe. The time may or may not be the same as our time and found it wasnt as reliable or accurate according to my time zone.

Share this post


Link to post
Share on other sites
mjhnson24

One other question... Do you know if there is a limit to the amount of data or number of records in JSON that filemaker can hold or get?

Share this post


Link to post
Share on other sites
Josh Ormond

Theoretically, it depends on what your machine can hold between RAM and disk space. Depends a little bit on what part of the process you are dealing with. I once tested a JSON object with over 2 million characters, so most anything we get back will be fine.

Share this post


Link to post
Share on other sites
mjhnson24
14 minutes ago, Josh Ormond said:

Theoretically, it depends on what your machine can hold between RAM and disk space. Depends a little bit on what part of the process you are dealing with. I once tested a JSON object with over 2 million characters, so most anything we get back will be fine.

Ok cool, thanks

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

Terms of Use