I've been using ChatGPT a bit to write queries. It's especially helpful for things where I have some level of repetition because of Filemaker limitations, but also does a great job of formatting SQL for me. I'm not a SQL pro so it definitely saves me time when I'm writing something with a join, or which has to be sorted in a specific order.
Where it seems to fall down is making up stuff. It tries to do things with LET statements (specifically with a LIST command and also with WHILE commands) that just don't work. Eventually, after much cajoling it will fix its code. I've told it a few times to remember what it did, but it doesn't seem to learn very well.
I'm wondering if other people have experience with AI writing code for Filemaker and what systems they prefer, and how well they work? I'm using 4o for what it's worth and haven't tried the preview of the new one (o1).
I've noticed the exact same issue with Let and While. It will try to run a While loop inside a Let, without declaring it to a variable.
Instead of using ChatGPT, I would use the OpenAI Assistant, and train a model with samples. I've done it several times for other languages and JS libraries. You just need a set of sample prompts and responses.
You can also take pages from the FMP docs and download them as PDFs, then upload them to the Assistant.
[EDIT] I just got the go-ahead from Brain Dunning to train a model on all the data from his site. Stay tuned! I'll update here when I have something useable.
[EDIT #2] - I'm leaving it as a tribute to Brian's brain.
I spent a few hours picking out 10 custom functions and formatting them as JSONL, then trained a 4o-mini model on it. Right from the start it had the same types of errors as before. But 10 is the bare min for fine-tuning, and the recommended dataset size is at least 50 examples.
It would be a lot of work to pick 40 more and format them property just to see if it gets any better. So I went with a custom GPT and uploaded reference files with several pages from the FMP docs, and an example file with the custom functions.
So far it does seem to be performing better than regular ChatGPT. I got it to write an SQL to JSON function that worked on the first try. Here is it if anyone wants to try it.
Please let me know of any issues you have, and what the prompt was so I can update the instructions.
Hmm, I haven't tried the Mac ChatGPT app. But I just tested this on web from an incognito window to make sure the custom GPT is public, and it seems to be working fine. You do need to login with your own OpenAI account, but then it should be accessible by anyone.
write me a script completely within a "let" statement which counts the number of base tables and tells me how many fields are in each one.
the first script had an error with too many parameters in the JSON, and the second had an error with two few, but then it got it when I reported the line and the error.
I tried the same thing with ChatGPT 4o and it just keeps messing up with the WHILE statement. I tried to correct it a few times and it couldn't figure it out.
I tried it with o1-Preview and it took a 34 seconds and it got it in the first try, and the code is much more concise. As an aside, o1 knows about the filemaker command "BaseTableNames" but the ChatFMP version uses SQL to find that, which makes it much slower. It takes a few seconds to run the script that ChatFMP produces, while it's nearly instantaneous for the one created by 01-Preview.
This is what ChatFMP produced
Let (
[
// Get the list of base tables
baseTables = ExecuteSQL ( "SELECT DISTINCT BaseTableName FROM FileMaker_Tables"; ""; "" );
totalTables = ValueCount ( baseTables );
// Function to get the number of fields in each base table
fieldCounts = While (
[
i = 1;
jsonFieldCounts = "[]" // Initial empty JSON array to store the field counts
];
i <= totalTables;
[
currentTable = GetValue ( baseTables ; I );
fieldsInTable = ExecuteSQL ( "SELECT COUNT(FieldName) FROM FileMaker_Fields WHERE TableName = ?"; ""; ""; currentTable );
// Corrected JSONSetElement function with type for outer array
jsonFieldCounts = JSONSetElement ( jsonFieldCounts ;
"[" & (i - 1) & "]" ;
JSONSetElement ( "{}" ;
"tableName" ; currentTable ; "JSONString"
);
"JSONObject" // Specify the type as JSONObject for the outer element
);
// Set the fieldCount for the current table
jsonFieldCounts = JSONSetElement ( jsonFieldCounts ;
"[" & (i - 1) & "].fieldCount" ; fieldsInTable ; "JSONNumber"
);
i = i + 1
];
jsonFieldCounts
)
];
fieldCounts // Return the JSON result with table names and field counts
)
This is what ChatGPT o1-Preview created
Let (
[
baseTables = BaseTableNames ( Get ( FileName ) );
tableCount = ValueCount ( baseTables );
result =
While (
[ counter = 1; output = "" ]; // Initialization
counter ≤ tableCount; // Condition to continue looping
[ // Logic executed each loop
baseTableName = GetValue ( baseTables ; counter );
fieldList = FieldNames ( Get ( FileName ) ; baseTableName );
fieldCount = ValueCount ( fieldList );
output = output & baseTableName & ": " & fieldCount & " fields¶";
counter = counter + 1
];
output // Output after each iteration
)
];
result // Final result after the loop
)
Thanks for this! BaseTableNames originated in v20.1 so that gives us a rough idea of the cutoff point for the 4o dataset, and what topics it needs extra training on. I can add some more reference files to the ChatFMP version. But it sounds like vanilla o1 could already be better than a 4o model with custom embeddings.
Does anyone have a PDF of the entire FMP docs for any version beyond 15? They used to release full PDFs but now they are just web versions. And exporting the web version only does that topic. It would be great to have all of v19 docs or newer in a single file.
So continuing to use the preview, it did put a While statement and a LIST statement illegally inside of a LET statement, but when I corrected it it "did the right thing".
OK so it does the While statement wrong, and when I tell it to fix itself it does, but then it breaks it again pretty much every time, even when I say "don't break the while statement but make this change". so maybe training would be the right solution.
Have you tried Claude or any other AI's yet? I'm going to try a few more before building a bigger training set.
So far, the training approach hasn't yielded any better results, but I only had 10 examples. I think we'd need 50-100 to see better results. Maybe we can crowd source a list of the best ones. Brian Dunning's site has ratings and vote count, but the posts aren't searchable by those metrics, so we still have to dig through and hand pick the ones to use.
I tried Claude once a few months ago and got worse results so went back to Open AI, which I have a subscription to. I've wondered about some of the ones that run locally, but I don't think I'm enough of a geek to "train" a model or run it locally yet.
UPDATE: I tried Claude, Cohere AI and Lamini AI, and all of them had the same problem with While() functions inside Let()! Lamini had the most interesting failed attempt, by trying to nest multiple JSONSetElements for every header.
I may still try Gemini and a few other AIs, but it's looking more like a job for training a model. I'm also working on a solution to filter the briandunning dataset by ratings and votes.
If anyone has examples of valid custom functions using While + Let that we could use in the training data, please post below.
I just DMed you a few samples. DMs are a pain b/c of character limits.
I feel like within 12-24 months the "right" solution will be to have a custom local model... but I could be wrong about that. Not sure what the interfaces for something like that would be.
