Calc that says "Yes" if record is in found count?

[ibiubu]

Is there a way to have a calc that determines if a record is part of a found count? Say I have 10 records in the found count...

I know I can have a field that uses Status(CurrentFoundCount) and will return 10

I can also put "@@" on the layout, and it numbers the records in order from 1-10

If there was anyway to do the equivelant of If("@@" < Status(CurrentFoundCount), "Yes", "")

Of course that is impossible, but it was the best way I could think of to describe what I was trying to do.

Larry

[rdhaden]

I don't know if this is good design or not, but when I'm working with a found set that I need to return to or otherwise get information about, I have a number field called InFoundSet. I find the records, then by script I set that field to 1 for all records in the found set. I also have a "clear found set" script that sets the ones back to zeroes.

[ibiubu]

I thought of that, basically looping through the found set and setting the field, but I started getting concerned about doing this if there are a large number of records, say 1500 or so.

LR

[ibiubu]

Basically what I am after is to be able to do a calc that would do something like:

If(this record is part of the current found set, "Yes", "No")

Larry

[Fenton]

Create a global field, _gFound IDs.

In your main table, create a layout with only the unique serial ID. Go to the layout, Copy All Records.

Go to a layout with _gFoundIDs. Paste. Commit.

Calculation field:

Case ( PatternCount (

[sbg2]

Couldn't you just search for both search criteria

[CobaltSky]

Hi Larry,

A couple of things. First, setting a flag field to "1" throughout the found set is a really bad idea if there is any chance that your solution will ever be shared on a server - because each user will see other user's 1s as well as their own - and when one user clears the 1s they will (presumably) clear everyone else's at the same time.

With this in mind, the method that Fenton is suggesting is a better option. Globals have separate values for each user in a shared solution, so there will be no conflict.

However since you're using FIleMaker 7, there is another option, and that is to capture the search criteria and place them in a field which is then evaluated using the Evaluate( ) function. So for instance if your users search for records with 30 in an 'Amount' field, capture the name of the field and the criteria entered into it (eg have a script that is used to execute the find which first writes out the criteria to a global text field) in the form of an appropriate expression such as:

Amount = 30

A calc field which has a formula along the lines of:

Evaluate ( YourGlobal )

...will then return a 1 for the records in the found set (ie those that meet the search criteria and a zero for all other records.

If your searches include multiple criteria and lots of find operators, then there may be a bit of work to translate them into an appropriate syntax in the global field - in which case you may prefer the mthod Fenton has mentioned, but if not, this method is an option that will avoid the necessity for an additional layout for each table.

[ibiubu]

Ray,

Sorry, my mistake. For this particular solution I am stuck using FM6. Thought I had chose v6 when I posted, but apparently not.

Fenton,

I assume that even though I made the mistake of posting using v7, your idea will still work with v6? Using what you said, I tried to re-apply it to version 6 but am not sure if I got it. In particular, SerialID? Must be a FM7 thing.

Larry

[CobaltSky]

Hmmn... the mistake was mine not yours, Larry.

Now you've mentioned it, I do see that against your original post, right down the very bottom, underneath where it says 'FMP: 7' it also says 'This Post FMP: 6'. I'm afraid I must have seen the 'FMP:7' and looked no further...

Since you're using v6 for this project, I'd give my vote to the method Fenton has described.

[ibiubu]

Fenton,

Will your idea will still work with v6? Using what you said, I tried to apply it to version 6 but am not sure if I got it. In particular, SerialID? Must be a FM7 thing becuase this is where I get lost.

Larry

[-Queue-]

SerialID is whatever field you're using as a unique record identifier.

[ibiubu]

Let me see if I have this...

1) I created a global field called _gFound ID.

2) I have a field called Record_ID which is a unique record identifier using Status(CurrentRecordID)

3) I created a calc field that uses Case( PatternCount( "

[-Queue-]

You only need a layout containing only Record_ID and one layout containing _gFound IDs (it need not be the only field on the layout). Go to the layout containing Record_ID and Copy All Records. Then go to the _gFound IDs layout and Paste into _gFound IDs. _gFoundIDs then contains all of the found set's Record_IDs.

You can also loop through the found set and append the Record_IDs to _gFound IDs, separated by "

[Fenton]

Easier to see in an example.

A couple more considerations:

Copy All Records requires Export privileges; hence could be a security risk if enabled for everyone. You can get around this in 7 by setting that subscript to [x] Run with full access privileges. In 6 you could build the list in the global field with a Loop instead. But it's not as fast.

There is a limit to the size of a text field. In 6 it's 64,000 characters. If your ID field and found sets are both large, that can add up. In 7 it's 2GB; not to worry.

FoundCountIDs.zip

[ibiubu]

Thanks for the example. Worked PERFECTLY for exactly what I needed.

Larry