I think that's a likely good general rule to live by, but with this very specific application, I don't believe that it holds its usual value. I do appreciate the heads up, though, and I will take a review of the code with you have said in mind, to assess whether following this idea would add or lessen the quality of the code.
Edit: I just checked the reserved string that I believe has been in place since the beginning. It is 8 characters long.
While I applaud your desire to give credit where due, please do not include me in your attributions. I got the idea of the BOM many years ago from Marcelo Pinyero in an article about sorting dynamic value lists. It is certainly not my discovery.
Is this not supposed to work with date fields? At least I'm struggling to get this working with a date field, e.g. WHERE Date BETWEEN ? AND ?:
Let([
~startDate = "01.01.2022";
~endDate = "31.12.2022";
~sql = JSON_SqlSelect( "" ; "" ;
List(
JSON_SqlField( "Date" ; Invoices::Date ; "" );
JSON_SqlField( "DocumentNumber" ; Invoices::invoiceNumber ; "" );
JSON_SqlField( "Company" ; Invoices::Company ; "" );
JSON_SqlField( "Amount" ; Invoices::totalAmountFromStock ; "" );
);
List(
"FROM Invoices";
"WHERE Date BETWEEN ? AND ?";
)
)
];
JSON_SqlResult(
ExecuteSQL( ~sql ; "" ; "" ; ~startDate; ~endDate );
True
)
)
I've tried to change the date format to many different possibilities (Month first, Day first, Year first, Date()) and also tried with WHERE Date = ?. The error is always:
There is an error in the syntax of the query.
Hi @aalars .
Thank you for the message, and apologies for the delay. I have been out of Internet/cell range for the past week.
Can you get the following ExecuteSQL query to work?
ExecuteSQL( "SELECT \"Date\" FROM Invoices WHERE \"Date\" BETWEEN ? AND ?" ; "" ; "" ; Date( 1 ; 1 ; 2022 ); Date( 12; 31; 2022 ) )
If you can get the above to work, chances are good that we can make the SQL --> JSON functions work for you as well.
If the above query returns an error, or an unexpected result, then the first troubleshooting step is to figure out how to make it work, and then from there, we can apply the SQL --> JSON functions and see what happens.
I'll be able to respond with less delay at this point, now that I am again at home near means of communications.
Is there a newer version of the Custom Function? I have the one from 2021, but in a couple of the replies, there was mention of updating it to include the BOM functionality.
Thank you for asking, @bowdendata .
I did create an adaptation with the change of making the reserved string use unlikely ascii characters (including a BOM char). That said, I never wound up testing that updated version enough to release it publicly. As a result, there is not an update beyond the one at posted at the top of this thread.
If there is interest, I can post the adapted version as a beta version, and anyone interested can try it out. That might be one way to get some momentum on an otherwise stalled initiative.
All that said, one of the reasons that I have not put a lot of pressure on myself to release the adapted version is because, despite seeing the use of control characters in the reserved string as an improvement, I have felt rather comfortable with the stability and reliability of the currently released version of the CFs.
this is a very good function, thanks for sharing.
newbie here
Hi @marke1415 . Welcome to the Soup!
Thank you for the interest in this function.
To get started with it, the best first step is probably to download the example file:
SQL_JSON_Demo_20210814_1310.fmp12.zip (821.7 KB),
and then take a look at the examples in the file.
There are a few examples in the "advanced" section which illustrate how the custom functions can be used with the aggregate SQL functions that ExecuteSQL supports (e.g., COUNT, SUM).
It's important to note, however, that since these functions are making use of ExecuteSQL, all of the same performance concerns that exist with respect to performing more demanding queries with ExecuteSQL also exist when using these functions. Thus, while the functions support the use of features such as COUNT, MIN, MAX, etc., whether or not the performance will be satisfactory is a question that has all of the same considerations that apply to a regular ExecuteSQL query without the JSON aspect.
Hope this helps!
thank you sir for a quick reply, i downloaded the file. few questions sir.
Hi @marke1415
Thank you for the reply.
Above all, I will reiterate an earlier comment of mine regarding performance being directly linked to the performance of the ExecuteSQL function. Upon first seeing these functions, it could be easy to imagine that they will solve many many applications because of the JSON output, and the flexibility of SQL. That said, the reality is that they work great in some cases, but there will be many other cases where what sounds good in theory does not play out very well in practice, because not all ExecuteSQL queries will be performant.
My advice would be to experiment a lot with them, including with small and large scales of data, and that will help you have a better sense of how they might be of practical use.
If you have any other questions, of course, please let me know, and I will do my best to clarify whatever I can.
Best wishes,
-Steve
JSON_sqlfield, JSON_SqlSelect and JSON_SqlResult
could you enlighten me on how can i run this functions in my application so that when i run SQL, it will give me result in JSON then i will pass it on for PIVOT table,
do i need to install or copy anything (because its giving me error "This function cannot be found") when i run it in my application.
Sorry for this, i just started developing in FM 3 weeks ago, i am still learning
thank you
Ah! I am glad that you mentioned your relatively recent adoption of FileMaker. That helps me understand a lot better where you are coming from.
Given that this is the case, my recommendation would be to back up a little bit before latching onto these custom functions, and give yourself a little time to digest more about what you can do on your own with scripting and calculations.
And then, I'd also suggest taking a look at a script step called Execute FileMaker Data API. The reason that I recommend taking a look at that script step is that a lot of FM developers use it to run queries and receive results in JSON, making it another candidate for a technique that could meet your needs.
At some point, you will also want to become familiar with what FileMaker Custom Functions are. Custom Functions allow for the encapsulation of calculation code into reusable/centralized locations within a solution. To use a Custom Function, it is necessary to either build it yourself within your solution file, or to either copy or import the custom function from some other FileMaker file into your own solution.
In the case of the functions that we have been talking about here, the functions that FileMaker said "could not be found" are custom functions, and so, before you can use them in your solution, you would want to copy them from the sample file that you downloaded, and paste them into your own solution file. Alternatively, you could also import the custom functions per instructions that are documented on Claris' site (see link above regarding custom functions).
But again -- I would suggest taking some more time to digest more of the options available to you before going down the very specific path of using these ExecuteSQL/JSON functions. My personal opinion is that this would be in your best interest as it would keep more doors and possibilities open to you.
I hope this may help.
Welcome to FileMaker! 
-Steve
thank you for a very detailed reply, i will be looking on that links you shared. i find FM is powerful and versatile compared to traditional platforms.
again thank you very much
FileMaker is definitely a very versatile (and fun platform), and the community of developers who work with it every day is a very supportive and innovative group. I hope you enjoy...
i was able to compare it to others because i am a developer that uses the traditional Php/MySQL/Javascript/JSon/CSS, at start i was amazed because it has lesser codes, most codes were supplied by FM. It is very different from what i am doing before and i am enjoying it right now. but i need to explore more.
hello again sir Steve, i copied 4 databases (i used import) and its scripts( copy and paste). but still the error in data viewer is function could not be found. am i missing something?
hello again sir Steve, i copied 4 databases (i used import) and its scripts( copy and paste). but still the error in data viewer is function could not be found. am i missing something?
Hi @marke1415,
What needs to be copied is the set of custom functions in the demo file. Copying the tables and scripts is not necessary (but, copying or importing the custom functions is necessary).
Documentation for how to import custom functions into a solution is here:
Once you successfully import the custom functions into your own solution file, you should be able to use them.
Best wishes,
-Steve
hi steve,
thanks for the assistance, i was able to run your custom function. I now have json data, how can i put this in a table. can you point me to right direction.
thanks again
Hi @marke1415 ,
Good job on getting the custom functions available in your solution.
Putting bulk data into a table can happen a few different ways:
It happens manually by using FileMaker's import/export functionality. This is something that would be appropriate for one-time use cases, and I will note that it would not involve having your data as JSON.
You can create a script (or scripts) in FileMaker to automate adding data to a table.
Manual entry of each record by a user.
If writing scripts in FileMaker is something which is new to you, that is where I would suggest directing your next steps for learning the platform. There will be other topics that you will need to pick up along the way, such as the importance of context in FileMaker. Context is so important such that I guess I'd suggest that you research/study that first, and then move on to scripting. Once you have a grasp of those two, you will be on your way to having the skills that you need to automate adding data to a table.
p.s. For further questions, I'd suggest starting a new thread, as the topic is likely to grow more distant from the gist of this thread.
i will do that sir. thanks
