GTRR Strange Behavior Error Reporting

[amgdba]

I have a solution that is attempting to GTRR on found sets to capture all children and flag them for export. I run my script, it works fine, then sometimes it doesn't.

I run it with the script debugger and it returns error 101 on the GTRR, but it goes to the related table and has a found set. I scratch my head and check the parent found set and sure enough these are children belonging to the parent found set, yet I get an error. I run this process for a few hours, tweaking a few items to see if it's an error on my part, corrupt data or indexes, so and so on. Then it occurs to me to run the script debugger and on the GTRR script step, nav the parent found set to a record that has a valid key field to a child (not all the records in the parent found set necessarily contain a child to relate to), and viola, no error.

So FM GTRR when used to relate to all children in a found, if asked to do so from a record within the parent found set without a valid value, will return an error of 101 but still execute the GTRR (very poor form if you ask me).

My question is has anyone who has experienced this found an elegant solution to overcoming the error without some ridiculous loop till you land on a parent that has a valid value prior to executing the GTRR?

[IdealData]

101 - Record is missing.

This also happens when you run a script with a loop and "Exit after last"

[Søren Dyhr]

We're talking GTRR(FS) ...aren't we?

It's giving you a warning that the present record doesn't have a match, while others in the found set though might have ... the error code changes to 401 if all records in the found set are childless, and the focus remains the found set of parent records. Why shouldn't it be perfectly safe to do something to the rest of the records pointed at in the child table?

So if your script just looks at the error code boolean'ly and not checking for the specific here 401 ...is your script going to fail it's purpose.

This change of focus behaviour, has it's neat uses and I for one wouldn't like it to go:

Things get interesting if you GTRR to a layout, and that layout is based on the same table as originating layout, and the layout has no connection to the originating layout.

...snipped from: http://www.sumware.net/robfm2/?p=4

Which is demo'ed here:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000754

BTW does it worry me slightly that you feel an urge to tag records at all ... could you explain why it's nessersary?

--sd

[amgdba]

Why am I tagging? Well that's a good question, but it has no clever technical solution. I have a management tool and an application that run separately and cannot be shared in a server environment. Different copies of the application will have different data provided by the management tool, and that data has to be imported into the application.

The fun part is the relational criteria is NOT standardized on just one PrmKey/FrgnKey, but rather two fold in nature when having to select a data set for export to the application from the management tool. I don't want two files for import based upon the same data source (Table), so I flag the first related set, switch context, flag the second related set, do that all over again for a few different tables, then perform finds on those tables for the flagged set and export everything lump sum.

Clumsy and annoying, but it ensures me that everything gets pulled.

I had not even considered the 401 error, since I always had data to relate to, so dropping the Get(LastError) <> 0 and actually checking for the 401 sounds ideal, thanks.

[David Jondreau]

This is a known issue:

http://www.fmforums.com/forum/showtopic.php?tid/190490/

http://fmforums.com/forum/showtopic.php?tid/191479/

[Søren Dyhr]

I don't want two files for import based upon the same data source (Table), so I flag the first related set, switch context, flag the second related set, do that all over again for a few different tables, then perform finds on those tables for the flagged set and export everything lump sum.

Have you ever thought of letting one single table act as different tables in the relational scheme?

http://fmforums.com/forum/showtopic.php?tid/175168/tp/1/

Perhaps it could have it's bearing in such a non standardized work flow?

--sd

[amgdba]

I am using a separation model already, but I didn't foresee this caveat in advance and of course I should just List an array to the interface file and then just relate to them all, but the relations can several levels deep and I'm in a hurry to make this happen. If I had the time, and I will probably end up doing it anyways, I'll pull the values in to the interface and use a single point of context to make it happen.

Thanks for the help everyone.

[Søren Dyhr]

I think you misread the link I made, what I suggest is to rely on recursive structures and letting one single table play all roles in the relational graph ... because it would simplify the sync'ing quite a bit - and it has hardly any reference to the separation model what so ever.

--sd

[amgdba]

My apologies, my understanding was to use the same table in different related contexts branching out. I had looked briefly at the thread you attached but it became quickly apparent that this methodology requires some strict discipline from the on set of concept if you want it to work, and the design rules for this solution are already decided upon.

I would have to start from scratch if I were to adopt this model. I have to say that I did something to the effect once, quite by accident (in terms of wanting a recursive structure) when making an application that revolved around a drill down type structure in a portal view. I ended up using a recursive structure with multiple keys to build a hierarchies that could be expanded and contracted. This type of work requires a keen mind and a VERY clear understanding of the "End" of the structure. I enjoyed doing it as an endeavor to push the limits of FileMaker, but I don't know that I would use it in a solution that I had to walk away from and then reopen months down the line when the client decides they want more work done. It's very heady and maddening material sometimes, and just the thought of having to go back and relearn everything I did months ago is just not appealing.

Now this is coming from me, and perhaps I'm a lazy slob, but does anybody actually use this with regularity, even without the need to relate recursively, ie the example I gave, with their solutions?

[bruceR]

How many records are we talking about? I'm considering recommending a record ID collector technique which is good for 200K records or so. Actually works OK with more but pretty speedy at 200K or less. I've done some rather extensive studies of record collector techniques.

[bruceR]

I have a solution that is attempting to GTRR on found sets to capture all children and flag them for export. I run my script, it works fine, then sometimes it doesn't.

I run it with the script debugger and it returns error 101 on the GTRR, but it goes to the related table and has a found set. I scratch my head and check the parent found set and sure enough these are children belonging to the parent found set, yet I get an error. I run this process for a few hours, tweaking a few items to see if it's an error on my part, corrupt data or indexes, so and so on. Then it occurs to me to run the script debugger and on the GTRR script step, nav the parent found set to a record that has a valid key field to a child (not all the records in the parent found set necessarily contain a child to relate to), and viola, no error.

So FM GTRR when used to relate to all children in a found, if asked to do so from a record within the parent found set without a valid value, will return an error of 101 but still execute the GTRR (very poor form if you ask me).

My question is has anyone who has experienced this found an elegant solution to overcoming the error without some ridiculous loop till you land on a parent that has a valid value prior to executing the GTRR?

Why change anything and why does this concern you?

The error code seems useful and accurate. It is telling you that some parent records have no children. This seems to be a very useful thing to know.

If Filemaker worked as you request, you'd never know this. Since you DID land on a found set, you know that at least some parent records had child records. If your expectation about your data was that ALL parent records in the set have child records - and you received the 101 error - then you would know that your expectation was incorrect and you could review your data to find the problem.

What might be better is if the error code better matched what you're doing - GTRR for a found set. I actually think that it should give a 101 error for GTRR found set no matter what record you start on - if there are any parent records with no child records. It is then up to you to interpret that.

[amgdba]

Again, I'm lazy and was looking for an easy pass through on the error. In the end you are all right, there are ways to get around this, so you just code for it. I can just capture for 401 or 101 and be done with it (though it wreaks havoc with pause on error).

As for the record collector technique, this solution, CURRENTLY (key variable), is well below that, but that does not mean there is no expectation to do that volume in short order (6 months to a year). I'm not very keen on the idea of thinking too much outside the box on this solution, but I would be curious to see the record collector technique.

[bruceR]

About 20K record ID/second with several of the methods. Don't know about the outside the box part; they use standard FileMaker features.

[amgdba]

In regards to how useful this error is, I was giving some thought to it, and I would agree if there wasn't one small issue, and that is consistency. Remember, if the current parent DOES have a related child, poof, no error, even if every other parent doesn't. This could be useful for trapping where every parent would have to have a child and you wanted to trap for it, but since its the luck of the draw pending the active parent in the found set, you can't depend on it, so again, some annoying additional coding would have to be in place in lieu of the error trap.

[Søren Dyhr]

This could be useful for trapping where every parent would have to have a child and you wanted to trap for it, but since its the luck of the draw pending the active parent in the found set, you can't depend on it, so again, some annoying additional coding would have to be in place in lieu of the error trap.

Yes it's actually a nice challenge to write something compact, taking care of this. It must be because of the sync'ing you need this, otherwise would i have a hard time time to see where else it might prove a problem?

I toyed a little with it, and there are probably faster ways to achieve it ... so regard it as initial thoughts in the direction.. but I see the saving of the initial fonud set the cardinal point here?

If the next objective is to steer free of native errors as it would be required in an IWP solution, must a looping throug the found set, just to get the first record with a child record present, and use it for focus ... this actually include a tiny shortcut, if arriving in the first record proves childless initially. But this have not found it's way into my suggestion.

I have left MainID 3 as childless, in the attached template:

--sd

test.zip

[LaRetta]

In regards to how useful this error is, I was giving some thought to it, and I would agree if there wasn't one small issue, and that is consistency. Remember, if the current parent DOES have a related child, poof, no error, even if every other parent doesn't. This could be useful for trapping where every parent would have to have a child and you wanted to trap for it, but since its the luck of the draw pending the active parent in the found set, you can't depend on it, so again, some annoying additional coding would have to be in place in lieu of the error trap.

Again, I'm lazy and was looking for an easy pass through on the error. In the end you are all right, there are ways to get around this, so you just code for it. I can just capture for 401 or 101 and be done with it (though it wreaks havoc with pause on error).

Hi amgdba,

If you don't care if some parents in a found set don't have children then it is simple. Set Error Capture [ On ] and then, if 'match current record' test for 101 only; if 'match found set' test for 401 only. You know which error might be thrown when you create the GTRR script-step. As for wreaking havoc on pause on error, you mean debugger? I'm not sure I understand why that is a problem. Even if it pauses because of a 101, you are watching it debug, right?

UPDATE: Personally, I think it is a bug that it only throws 101 if the FIRST parent doesn't have a child. 101 should throw if ANY parent doesn't have a child (when matching found set).

Edited May 3, 2010 by Guest
Added update