Error 401 problems

[elipsett]

Having a bit of trouble getting the find function in a script to run. I can't really post the script here because I'm using the Japanese-language version, and most of you wouldn't be able to read the text, but the only problem is with the find function.

I have two tables in a single DB. Table A is list of people I need to pay, each person with a unique ID (text). Each record is marked with that ID, and a calculated field saying "Paid" or "Unpaid." This list is used to actually calculate the total amount payable to each person that month.
Table B is a list of payables, each record for a single payable, and marked by amount and ID. 

The script is supposed to go through the list of payables, one at a time, and only see if that ID already has an open (Unpaid) record in Table A. If there is a record, the script loops and moves to the next  record in Table B, repeat. If there is no record in Table A with that ID marked Unpaid, then the script creates a new record in Table A with that ID and marks it Unpaid.

The script looks at the current ID in in Table B,  and assigns it to a variable $$ID.
Goto Table A and search for ID=$$ID AND Status=Unpaid.
This is where the problem occurs.

If there is a matching record, it is found correctly, and the program correctly ignores it, whether error capture is on or not.

If error capture is OFF and there is no matching record, FMP returns an error message saying no records match the criteria (correct).

If error capture is ON and there is no matching record, FMP says error code=0 and Record count = 1 (the screen at that point shows a layout with no ID displaying). Prior to the FIND, the DataViewer shows the correct settings for ID and $$ID. If I search for either criterion the script works, but I need to search for both. I've been trying to capture either the error code (401) or find count (should be zero), but they always return Error=0 and Count=1 whether the matching record exists or not. 
Obviously I'm overlooking something.

I think that's enough information...

Any suggestions on what I did wrong?

 

Thanks for your time.

[comment]

3 hours ago, elipsett said:

If error capture is ON and there is no matching record, FMP says error code=0 and Record count = 1 (the screen at that point shows a layout with no ID displaying).

Could you describe this part in more detail (the exact script steps in their exact order, and what do you use to capture the error)? My interpretation of  'error code=0 and Record count = 1' would be that one record matching the find criteria has been found - yet you say that 'there is no matching record'.

[elipsett]

I remembered that I can change the program language, vastly simplifying things. I attach a screenshot.
Various field names are still in Japanese, tho... sorry...

The table on the first line is Table B, line 6 is Table A.
The problem is in line 8, which searches for
ID=$$ID and PaymentStatus=Unpaid

The IF statement after the find creates a new record if one is not found, and the rest is just cleanup. 

The dialog is there for troubleshooting, so I can see what error code was returned. When error code handling is ON, it always shows code=0 count=1, and the displayed layout is blank (in the same way it would be blank for a search). 
My current guess is that for some reason the $$ID is not being searched for, and instead it is searching for ID="". 

[comment]

Let's go back to the critical point here (everything else is but a distraction). You search for :

ID=$$ID and PaymentStatus=Unpaid

and your custom dialog says 0 error, 1 record found. But you say no records were found. One of you is wrong, and I am not sure exactly what evidence you have to support your claim. Could you halt the script at this point and see what the found set is?

Also, what happens if you perform exactly the same find (using the same ID value) manually?

--
Note that if you're right and Filemaker is wrong, then your file is most likely corrupt. That's not a pleasant conclusion to draw, so let's try to find something that will show that you're the one that's wrong.

[elipsett]

So, replace the dialog line with a halt?
That should be possible.

[elipsett]

OK, I did that.
The script halted immediately after the find. The layout is displayed with all fields blank (like a FIND with nothing specified yet).
DataViewer says that $$ID is set to the correct ID value. 
Since there is no unpaid Table A record with that ID, I think this is the correct action.
The record count shows that it is displaying (0 of 2000) records of the entire set, which (again) appears to be a FIND screen.

If I disable the HALT line and run the script, it proceeds normally through every record in Table B, reporting the correct $$ID for every one, but creates no new records in Table A.

It is very unlikely that the file is corrupt... there are dozens of other scripts in this file, or calling it from other files, and there have never been and still aren't any problems with any of them.
I'm just trying to automate a task that is done manually at present.

 

Edited February 24, 2019 by elipsett

[elipsett]

Hmm.

I created a new text field Text01, and a 2-line script.
If I search manually, FMP correctly either finds it or reports no such record can be found.

If I run this script and the record exists, the script finds it, and the dialog is 0 and 1.

If the record does not exist, the script shows a form with all blank fields, and the dialog is STILL 0 and 1.

[comment]

Let me clear one thing off the table:

The way your first script is arranged, the If [] section will never be executed, because calling a custom dialog will never produce a 401 error. You must capture the error immediately after the step that produces it.

However, that still leaves the problem of the custom dialog reporting 1 found record when no records are shown. I would suggest you make a copy of your file, do a recovery on it, and see what you get.

[elipsett]

It occurred to me that the dialog might be an issue, and I tried disabling it, too. 
The script functions the same.

I made a new DB with VERY simple scripts.
I think there are only two possibilities left:
- I am making a fundamental error in this code

or

- My copy of FileMaker is broken
 

test.fmp12

[comment]

17 minutes ago, elipsett said:

I made a new DB with VERY simple scripts.

Okay, that is a good move. But there is no error capture or custom dialog in these scripts. And the 'find three' script will not set any fields, because there are no records in the found set. Can you make your new file produce the original behavior, namely a dialog reporting 0 error and 1 record found, when in fact no records were found?

[elipsett]

So apparently my understanding of error capture is wrong...
What would I add to the find3 script to get it to return an errorcode=401, since there is no target=3 record?

Edited February 24, 2019 by elipsett

[comment]

Run  the 'find three' script in the attached file. 

test+.fmp12

[elipsett]

It does indeed return a 401 code... but only in the dialog immediately after the FIND. The setfield lines are not working.

Let me fiddle a bit more...

I just added code to catch 401, and it does appear to work... just like I would have thought my original script should have worked.
Time to try again.

Thanks!

Edited February 24, 2019 by elipsett

[elipsett]

That is exactly how I wrote the first iteration of the script.... 
I changed my test.fmp to work properly, and added code to do X or Y depending on whether the error code is 401 or not, and verified it.

The code in my original script is exactly the same, with the single exception of the FIND line itself, which compares a

- Table A text ID to a $$ID variable created from a Table B text ID

AND IN THE SAME FIND

- Table A status = fixed text string "paid"

I'm going to make a simple file to test this. Maybe find and errorcode handling don't like variables?

The test file appears to work perfectly, drat.
Time to call it a night.

Thanks very much for your assist!
I'll try to find a solution, and let you know.

[comment]

11 minutes ago, elipsett said:

The code in my original script is exactly the same, with the single exception of the FIND line itself, which compares a

- Table A text ID to a $$ID variable created from a Table B text ID

AND IN THE SAME FIND

- Table A status = fixed text string "paid"

I don't think any of those differences should make a difference. Either the record you're looking for is there, or it isn't. If it is, it should be displayed on the layout, and the toolbar should show 1/2000. If it isn't, the custom dialog should show error 401 and found count of 0. That's the discrepancy that you need to focus on.

 

 

Edited February 24, 2019 by comment

[elipsett]

Still trying to figure out what the problem is.

When I run this script in a test file, it reports a 401 if the specified field value is not found. The field names have been made identical to those in my real file.
When I run the identical code in the real file, the first dialog shows LastError=0 Foundcount=1 (1 of 2000 records), and the screen is the find screen with the ID number (4182) showing, all correct. In other words, there is currently one record (the FIND screen).
There is no record with that number, so the second dialog should say 401/0. It DOES say 401/0 in the test file, but in the real file it says 0/0, and the screen shows 0 of 2000 records. 
If I manually enter the SAME find (4182), it says "No records match this find criteria."

Is there any possible explanation for this behavior?

Assuming the only possible explanation is file corruption, is it safe to assume I can make a zero-data copy of the file, and then bulk copy over all data from the old file to the new file? Or is that approach likely to reproduce the corruption as well?

[comment]

Earlier I suggested how you can verify the cause is file corruption:

On 2/24/2019 at 1:03 PM, comment said:

I would suggest you make a copy of your file, do a recovery on it, and see what you get.

If the recovered file behaves correctly, then the original is corrupt.

 

18 minutes ago, elipsett said:

is it safe to assume I can make a zero-data copy of the file, and then bulk copy over all data from the old file to the new file?

No, it is not safe to make such assumption.

[OlgerDiekstra]

In this script, the first Custom Dialog will never show an error. The 'Set Error Capture' command generally returns without error and therefore resets the triggered errors. The better position for the 'Set Error Capture' is at the beginning of the script. You also don't need 'Set Error Capture' in order to capture errors, it's purpose is to suppress the dialog.

Even without the 'Set Capture', you wouldn't capture a search error because no search is performed at this point, the 'Set Field' step would set the error, either to 0 or a non-zero value depending on whether the set field was successful or not.

Up until the first Custom Dialog, no search is executed. A search doesn't get executed until the 'Perform Find' step is executed.

However.

The 'Perform Find' step has 'Restore' enabled, which means it will find whatever is specified within the 'Perform Find'. The previous 'Set Field' step after the 'Enter Find Mode' is never included because of the 'Restore'. You have to turn the 'Restore' off to use the 'Set Field' step.

So, the expected behaviour of the script is to always return a lasterror of 0 in the first 'Custom Dialog'. The foundcount would vary depending on the initial found set when the script started.

The second 'Custom Dialog' would return either a lasterror of 0 or 401, depending on whether any records were found. The foundcount would return 0 if nothing was found, or the number of found records.

[elipsett]

19 hours ago, comment said:

> Earlier I suggested how you can verify the cause is file corruption:

Sorry, apparently I didn't mention that... I did perform a restore, and no errors were reported. The problem persists in the recovered copy. 

 

[elipsett]

I believe I followed your explanation, and tried it as shown. Results are the same (error code = 0) even when the target record does not exist.

If I manually set the two fields and search with the specified values, it reports no records match the find criteria.

Have I made an error in rewriting the script as you suggested?

[elipsett]

Update:
If I use FoundCount=0 instead of ErrorCode=401, the script works perfectly.

[OlgerDiekstra]

15 hours ago, elipsett said:

I believe I followed your explanation, and tried it as shown. Results are the same (error code = 0) even when the target record does not exist.

If I manually set the two fields and search with the specified values, it reports no records match the find criteria.

Have I made an error in rewriting the script as you suggested?

Yes. The first Custom Dialog does not tell you anything as the Perform Find has not been executed yet. Once the Perform Find has executed, then you can check for any errors.

The find will only return a record if there is a record where both fields have the specified value. Ie, the first field is '4294', and the second field is whatever that is in Japanese(?). I can say 'konnichiwa', but that's about it. 😄

15 hours ago, elipsett said:

 

Generally, you enter find mode, populate the fields with the values you need, then perform the find. Once the find has been executed, you can check for errors, and check if any records have been returned (foundcount).

You can use either just the 'perform find' script step and populate all the fields within it, or use a separate 'enter find mode' step, populate all the fields using 'set field' script step and perform a 'perform find'. I prefer the latter approach even though it needs more script steps as you can see at a glance in the script what its looking for. Whereas with a single 'perform find' you have to open it up. A single 'perform find' may be faster though.

[elipsett]

When I said it didn't work, I meant no 401 is ever returned. I also tried removing the ErrorCapture=ON and dialog steps completely, but it still will not capture 401.
If both fields are found, the script works (no error code), which is what I want. The problem is that it does not return a 401 if there is no record with both fields found. It always returns errorcode=0 on BOTH dialogs. (The dialogs are only there to try to see what the script is doing; script functions the same way even if they are both disabled).

I used the setfield commands deliberately to try to clarify exactly what the script is doing; normally I would use a single execute find command. 

If I use foundcount=0 instead, however, the script functions perfectly.

Edited February 27, 2019 by elipsett

[OlgerDiekstra]

All the 'Set Error Capture' does is disable the dialog. By turning it on, errors are not displayed to the user. You can still capture them in the script.

I created a similar script in my DB which returns the expected 401 when no record is found. Which might point back to Comments suggestion that the DB is corrupt. Perhaps export that particular table to a CSV file, import it into a new DB and see if that makes a difference. Could it be possible FileMaker is tripping up on some Japanese character?

[elipsett]

Restore completed with no errors, and multiple other scripts (internal to this file, or calling it from other files) all work perfectly, and have worked for at least a decade. It is unlikely (but certainly possible) that some internal error is causing all this. 
If FileMaker is tripping over a Japanese character I would be astonished, and FileMaker would probably be bankrupt.

A manual FIND results in a 401, but performing the same FIND programmatically does not. 
I can only assume that internally they are processed differently.