finding range using less than symbol

[KLA]

I'm having difficulty using the find command and "<". I have a calculation field (in a portal) that calculates if my clients paid less than their monthly payment. To do a find on who is short this month, i'm finding on this months date (on the date field in the portal (which has the date - MM/DD/YY)) and the payment calculation field using <0 - meaning they owe money (the calculation field will show a negative balance so i want it to find the negative balance). I'm plugging the info in on a singal find request. It's weird. i can't figure out the logic it's using. it worked fine when i had a few test records when i first tried it a month ago. Now i have 30 records and it's not working properly. It seems to find the greater than 0 records ok and the date records alone ok. But when i combine the date and less than 0 it will still bring up records that equal 0 or are just over zero like if they owe $.05. Any suggestions?

[Vaughan]

Try doing the Find in the related database rather than the portal. Bring the date and other necessary information across with calculated fields in the related records.

[KLA]

Thanks for the tip about doing the search in the related file. It does work this way. But my problem is that i need to be able to search this info in the Master File - it's where all my "unrelated" job info is for the client and where my invoice layout is. Isn't there a way to search for the info in the Master file? It did use to work and the FM book says i should be able to do this. What's the logic behind this?

[ibaxter]

I have a similar problem and I can't do the search in the related database. I have two datasets one (related) with 8000 records and one (master) with 25000. There are 7000 records shared between the two databases (with Unique IDs as the relationship). I want to search for records that are in both datasets and have a value in a calculated (in the related database) less than -2.

I have made a layout in the master database that includes the fields of interest from the related database. When I go to that layout and do a find and put < -2 in the correct field. I get a subset of the records where the value is -2 (as expected). BUT I also get a few records where the value in the selected field is >-2 !!

Can anyone tell me what I am doing wrong? Or a better way to do it?

Ivan

[djgogi]

First to explain what is the find on related fields (with or without an portal)

Think on this:

When performing any kind of Find you are searching for records in CURRENT data base that satisfy the criteria you've entered.

So why it should be diferent if some of fields come from relationship from another data base?

And in fact there is no any diference.

The result of find on relational field(s) (thru portal or "stand alone"), if any, will be the set of records in master file (the file wher you are performing the find ) whose children records satisfy the criteria you have entered.

Obviously in case of one-to-many relationship, if master record (that result to be in found set) has many children and not all of them fulfill the criteria used in Find, they will be however displayed in portal

When I go to that layout and do a find and put < -2 in the correct field. I get a subset of the records where the value is -2 (as expected). BUT I also get a few records where the value in the selected field is >-2 !!

I think it explains above, don't you.

So how to achieve the result you need?

One methode could be to define an auxilary relationship that includes additional factors like ID & "some data" where some data should match the "CALCULATED" value in child file like amount<0, then perform the find as you always have done but present the result on the layout with portal from this new relationship.

Dj

[ibaxter]

I guess I am not explaining this correctly (or not understanding correctly )

The relationship between the databases is one to one created on a uniqueID field within each database. I want to search through subset of records with UniqueIDs which are contained in both databases.

I thought that using a field that is linked by a relationship would allow me to only search the records in common between the databases. Is this not the case?

Let me spell out the situation in more detail than my last post....

I have 2 data bases:

A which has 25k records each with a UniqueID.

B which has 8K records each with a UniqueID

7K of the uniqueIDs are the same in each database. this is the set of records I want to search through. I want to be able to do many different finds, specifying different conditions, so I would rather not have to create a new relationship each time I want to pull out a different set of records.

So I created a layout in A and put a calculated field from B (B:calcfield) into it. The relationship used is A:uniqueID to B:uniqueID

Now if I go into database B directly and do a find in calcfield (>2) I get 240 records which all have a value >2 for calcfield. So far so good.

If I then go into database A and go to the B:calcfield and do the same find (>2). I get 220 records, 200 of which have a value >2 for B:calcfield BUT 20 of which have a value <2 for B:calcfield.

So it is returning records that don't match the criteria that I have specified. And this is my problem. I have checked that the relationship is indeed one-to-one. The spurious records don't share a uniqueID with the records that are getting pulled out. It isn't searching the current database, becasue the field that I have selected doesn't exist in the current (A) database

Does this explain my problem better?

thanks

Ivan

[djgogi]

It isn't searching the current database, becasue the field that I have selected doesn't exist in the current (A) database

You ALWAYS search for records in the current DB!

Only in case of true one to one relationship where EVERY master record has it child and there are NO ORPHANS the search COULD produce (after additional GTRR step on ADDITIONAL relationship based on GLOBAL field containing ids of every master record in current found set) implicitally the corresponding found set in child file.

Now you are saying that :

Now if I go into database B directly and do a find in calcfield (>2) I get 240 records which all have a value >2 for calcfield. So far so good.

If I then go into database A and go to the B:calcfield and do the same find (>2). I get 220 records, 200 of which have a value >2 for B:calcfield BUT 20 of which have a value <2 for B:calcfield.

This means that:

1) in B there are records that satisfy criteria specified but that don't have parent, iow they are orphans

2)the realtionship is not one to one, probably there are records in A that have many children.

To control the second point create an value list in B as values from field ID and the following calculation:

WordCount(ValueListItems(Status(CurrentFileName), theNameOfAboveList))=Status(CurrentRecordCount)

If the above calculation don't evaluate to 1, than there are more records than ids.

Dj

[ibaxter]

This means that:

1) in B there are records that satisfy criteria specified but that don't have parent, iow they are orphans

but the records that I am getting don't satisfy the specified criteria and they have parent records.

To control the second point create an value list in B as values from field ID and the following calculation:

WordCount(ValueListItems(Status(CurrentFileName), theNameOfAboveList))=Status(CurrentRecordCount)

I wasn't able to get this to work. I created the value list in B, but I dont' understand where to put that calculation.

Is there another way to set this all up?

[djgogi]

Ok, so you've said :

when performing find on >2 in child DB (: you've obtained 240 records, while the same find in master (thru related field) gave you 220 records ( MASTER RECORDS to be exact) with 200 having the right value for related field and other 20 wrong one.

So you have 40 records (in child db) that are orphans (240-200=40)

The other 20 MASTER records that have had the wrong value (iow <=2) for related field MUST have more than one child record.

At list one of those CHILD records for every (master record) MUST have had the right value for field you have used to perform find, because otherwise they would have not even appeared in found set when performing find on related field.

Now that part could be checked with the formula I gave you.

To implement it, simply create an calculated filed in child DB, let's call it oneToOne, set result to be numeric and memory storage unstored:

WordCount(ValueListItems(Status(CurrentFileName), theNameOfAboveList))=Status(CurrentRecordCount)

where "theNameOfAboveList" is the name you have givewn to the list of all ids in child file

Dj