It doesn't look like you are advancing to the next record by using "Go To Record [Next]".
There is a thread on this:
so you would do this
[ _q = "
SELECT table1.ID, table2.ID
FROM table1, table2
; _r = ExecuteSQL( _q
; Char(9) ; Char(13)
If you place into a field the result and copy. Paste into a text editor then import as tab-delimited. All your records will be created!
Thank you for the quick response. I am not sure where I would insert this into the script. I have tried many combinations using the record request, but none have worked except to advance only from the first record to the next. So I end up with only the first two IDs from table 1 and table 2. Every time a layout is entered it goes to record 1.
Thank you for your reply. I had a solution that used SQL and a series of import/export scripts. However, I need to deploy this solution in WebDirect and as a runtime. WebDirect will not support the import/export scripts, and Execute SQL is not supported in the runtime. The looping script is the only other solution that I can think of.
For me, and this is just my own individual personal opinion, I'm not a fan of the trend in these forums to try to convince new FileMaker developers to use SQL. Almost all of the FileMaker developers that I know specifically chose FileMaker because it is easy, intuitive, is as familiar as speaking the English language, and so that they could AVOID things like SQL.
I would recommend using FileMaker Pro Advanced. In FileMaker Pro Advanced, you can use the built-in Script Debugger to run through your script one line at a time, so you can see specifically where things are failing for you and where you should insert the "Go To Record [Next]" script step.
this is ExecuteSQL() - a function and should be able to be used everywhere (NO ODBC needed). But you are correct in the import/export limitations.
Looping will also do just fine, it's what we used before ExecuteSQL().
for a quick way (one time) the ExecuteSQL() was supplied, so nothing to "learn". As this is not a one-time usage, it doesn't matter. However, teaching new things is never a bad idea. It can always be ignored.
I have used the debugger and it appears that it is failing at exit loop if step. It s moving past both exit loop if statement and going to end loop. I have a functional script using SQL to set the counts and variable, but under a different data structure. I cant figure out why it is not working here.
Let's answer the man's question rather than debate the pros and cons of SQL. lol
Ok add the following lines:
1) After line 8 “Go to Layout”
If [ $i ]
Go to record/request [next exit after last]
2) After line 12 “Go to Layout”
If [ $j ]
Go to record/request [next exit after last]
Go to record/request [first]
The if statement is to keep this from happening on the first run through each time. Since you are setting/resetting your counter variables to zero (0) each time then you know that your first run through then your counter is zero (also meaning boolean false) the rest of the time they will have a number (boolean true) which will run the code inside the if statement.
Go to next record is getting you out of the loop you are in.
Go to first record is to reset your context in that table (your inner loop) so that you match each of those records with the second and so on. Without this line you will get the following:
1 A, 1 B, 1 C, 2 C, 3 C, etc..
Right now from this code you are getting a table full of 1 A, 1 A, I would assume
Edited: added else go to first record and explination.
I don't see any reason why the loop script would not work as a solution. It seems like a straight forward process, but I have tried over a dozen combinations and none of them work. I am at a loss for what to try next.
Oh yes, I totally agree that learning new things should always be a part of our daily rituals! I try to learn 3 new things every day, and I make a conscious effort to re-learn things that I thought I once new because I probably didn't learn it right the first time around. That's why I keep re-reading the FileMaker 15 documentation over & over again. It's just my personal preference that I don't bring up SQL to brand new developers because I'm worried that I may scare them away from our beautiful, simple, dead-simple and dead-easy platform of intuitiveness. That's just my own way of doing things.
It's because you're not advancing to the next record, so you're getting yourself stuck in your own loop because it keeps operating on the exact same record over & over again.
OK. So this gives me 1A, 1B, 1B. When I step through the script each time I enter the criteria (table 2) layout it is on the 1st record. It never goes back to the KPI layout (table 1). It terminates once it hits the record count for criteria (table 2). I did change how it was counting records. The value count from the script in the original post only resulted in 1A.