Problems with look ups

[Joe90]

I am looking up info in summary fields and instead of getting the info from a found set of records I am getting the info from all records in the related database.

 

Example database1 has a

field CarMake

and a Summary field Total Number of CarMake

 

If I have a 1000 records in this database

 

I search for Ford in CarMake and I get 122

When I do tyhe lookup I import 1000 instead of 122

[Ender]

Yeah, summary fields aren't very useful as a source for a Lookup.

 

Usually this type of thing is done by filtering the relationship between the two tables by whatever your criteria is, and then using an aggregate function, like Sum() or Count() to total the related values.

[Joe90]

Thanks for the info I am importing the found set of data into a holding file and then into wy final database this is working but seems long winded has anyone got any better ideas

[Ender]

Thanks for the info I am importing the found set of data into a holding file and then into wy final database this is working but seems long winded has anyone got any better ideas

Why are you importing the data to a holding file? What's the trouble with using relationships?

[Joe90]

I tried for an hour or so using your idea but with no success. I must be missing point.

[AHunter3]

What Ender said. You're going about this wrong.

 

Example database1 has a field CarMake

 

That part's good. Now if you want to know the sum total of all records where CarMake = and have it appear in a field, you can

 

Define a relationship between the field into which you typed "Ford" and the field CarMake, which will create a table occurrence; define a number constant, "One" defined as =1; define a calc field, TotalSelectedCarMake, = Sum(ThatTableOccurrence::One); and put TotalSelectedCarMake on your layout where you want to see the total.

 

If, instead, you want the total of all records in the current found set where CarMake = , but your current found set doesn't necessarily consist of all records where CarMake="Ford", you can:

 

Define a global field g.CarMakeChoice, which is where you will enter your choice, in this case "Ford"; define a numerical calc field, One_ifCarMake, = Case(CarMake=g.CarMakeChoice, 1); and define a Summary field, defined as the total of One_ifCarMake.

 

If, instead, you always expect to do a Find in the CarMake field for , so the found set will consist of all records where CarMake is what you're interested in, and you just want a field on the layout reflecting the total number of records, all you need to do is:

 

Define a calc field, unstored, numerical, = Get(FoundCount). There ya go.

[Joe90]

Thanks I will have a look at that tomorrow and will report back

[Joe90]

sorted now many thanks