Find Script to give (0) Value if No Records found

[Corey-Troy]

Is there a step i can add to a script, that IF there is NO Records found, then I get a (0)Zero value that i can paste into a field?

At the moment, if NO Records are found i Get the dialog box asking to Modify/Continue/Cancel Find.

Any Help appreciated...

Corey....

[SteveB]

Put in prior to doing a find: Set Error Capture(ON).

I usually put it at the top of the script along with Allow User Abort. This will allow the script to handle the error. Immediately after the find use a 'Get(FoundCount)' to find out the number of records in the current found set (located in the Get Functions subset). You can store this in a Set Field or use it in an IF.

Just remmber to do it immediately after the Find.

Steve

[LaRetta]

And a bit more ...

1) Don't include a comment between the Set Error Capture[] and the Find; as Steve indicates, place Error Capture right before the Find.

2) If you run a sub-script between Error Capture and the action you are attempting to trap, it will nullify Error Capture.

I'm unsure if this still holds true in vs. 8 but I suspect it does ...

[Corey-Troy]

Thanks for the reply guys.

I can get through the script without an error, but still not the true value i am after. I am beginning to think that i may be approaching this wrong??

My aim is to gain a total of values from across ALL Records that fit several Find criteria's.

EG.

Table 1.

[site]-text

[width]-number

[length]-number (43 repetitions)

[total length]-Sum([length])

[type]-text

Table 3.

[site]-text

[type]-text

[sw width 900] -number (specific width/type)

[sw width 900] -number (specific width/type)

[rp width 900] -number (specific width/type)

[sc width 900] -number (specific width/type)

{I have the same as above for 1050/1200/1500/1800, as well as the 900 examples above}. IE. 5 sizes(900-1800) in each of the 3(sw/rp/sc) types. Totalling 15 FIND Senario's all up.

I was trying to script Each Find, to Find only say:

Case([table1::site]=[table2::site] and [table2 type:]=[table1::type] and [table2::width]=[table1::width];Sum([table1::total length]))

So if a FIND finds a match in the current [table2] layout [site]and[type]and[width] fields in [table1] [site]and[type]and[width] fields, i then get a Sum of [table1:length] field and this is placed in a [table2] field specifically for that size/width/type.

What is the most efficient and best way to carry this out??

Script or Calc ??

Remembering i need 15 of these FINDS to populate 15 specific fields from [table1] into [table2].

I am going a little stir crazy, with trying to nut this out, with Relating different field to each table and also with CASE functions to no avail thus far.

The reasoning behind the requirement will save me a lot of data entry, and in particular remove HUMAN ERROR from data entry, by automating different values within the framework of these layouts.

I must admit i have learnt more about the workings of relationships, but have much MORE to learn before i perfect this task.

All comments and advice is very appreciated to assist me accomplish this dilema.

Thanks in advance....

Corey....

Edited June 9, 2006 by Guest

[LaRetta]

Remembering i need 15 of these FINDS to populate 15 specific fields from [table1] into [table2].

It doesn't sound like you are taking full advantage of a good relational structure. Whenever you find yourself horsing the data, it usually means something is wrong and your structure is whacky. Can you post your file?

[Corey-Troy]

I have attached what i have to date.

I placed some explanations of what i am aiming for on each layout.

Corey....

[LaRetta]

Hi Corey,

You have repeating fields throughout your solution! I like repeating fields, but NOT for data. The best way I can help you is not to help you with this structure. You need to start over, as has been suggested before by Soren here. You already have over 30 calculations in Referb Belt in an attempt to gather back the information you require. And this is only the tip of the tip of the iceberg of problems you will encounter. You will never be able to generate reports and you will continually be adding even MORE calculations and you will hit a point of impossibility. I simply can't stress this strongly enough ... change your structure NOW.

What would I do if I were you? Hire a Developer (and no this is not a plug) to help you establish a good base structure. You will find that, once a good base is in place, everything else flows naturally and is quite easy. Can you create a proper base structure yourself? Of course. But it will take you time to learn relational theory. I can't even provide you with a sample structure because your tables/fields make NO sense to me. I'm sorry I can't help you - I surely would like to.

LaRetta

[T-Square]

And a bit more ...

1) Don't include a comment between the Set Error Capture[] and the Find; as Steve indicates, place Error Capture right before the Find.

2) If you run a sub-script between Error Capture and the action you are attempting to trap, it will nullify Error Capture.

I'm unsure if this still holds true in vs. 8 but I suspect it does ...

LaRetta--

The Set Error Capture step can be placed anywhere in the script before the spot where you want to test.

What you have to capture immediately is the Get(LastError) status; this is indicated in the FM help screen, which says:

"Use Get (LastError) immediately after the script step you intend to test; a successful intervening step may clear the error code you were intending to capture"

David

[Oldfogey]

I suggest that it is just good practice to place the two error-trapping steps as close as possible to the step you are monitoring. I cannot think of one good reason for having a script step between the Set Error Capture and the Find. Sometimes you might want to delay the action to be taken based on the 'error' result. In this case you store the result immediately after the Find and test the stored result later.

Edited June 11, 2006 by Guest

[Genx]

And we're looking for error 401 if we use Get(LastError) rather than If[Get(FoundCount) = 0]

[Oldfogey]

Two issues there, Genx.

First, maybe your memory is better than mine but I rarely use error numbers because I can't remember them all. Also, as I'm only interested in whether or not I've found some records, I don't really care what error code was returned; it might well be an ambiguous one, or we might have no records because of a genuine error other than 401.

Second, never trust developers. There are three possible(?) value ranges for FoundCount. These are -n, 0, +n. I'm only interested in whether or not there are some. Hence If[FoundCount > 0] is nice and safe. Many of my grey hairs are from checking for =0 instead of <1 or >0.

Hence, if I needed some steps before acting on the find result, I would put a Set field[G_Field; get(FoundCount)] right after the Find.

[Genx]

a) I only remember a few error codes, : what on earth is -n? Thats a real question, i tried trapping for get(foundcount) = 0 on a few reports and they havent been working that great, and i havent been able to find out why...

[Oldfogey]

I don't really think you need to worry about -n (minus n) in this case but it is conceivable that there is a bug in FMP that, under some weird condition, will give negative result for Get(FoundCount). As I noted - never trust developers.

I always(?), as far as possible, when I remember, when it not inappropriate, make tests into range tests - above or below, but rarely equal to, a value. It costs nothing and the test is then a genuine binary test - no ambiguity.

How are your traps not working? My bet, from a state of total ignorance, is that your Find is crook. :btdt:

Edited June 15, 2006 by Guest