Can FM 6 lookup a value based on criteria?

For example:

In a spreadsheet, I would have say 5 Cols A&B and 5 rows 1-5

I would look for a match for column A and would return the value for Col B!

To make it more difficult, the match needs to fall within a range for
example:

Loan amount between �50,000 - �100,000 Return X
Loan amount between �101,000 - �150,000 Return Y

Etc

Thanks

In article <fqknc.1386$IM4.1029@nurse.blueyonder.net>, "Falcon"
<info@kando-mortgages.com> wrote:

> Can FM 6 lookup a value based on criteria?
> 
> For example:
> 
> In a spreadsheet, I would have say 5 Cols A&B and 5 rows 1-5
> 
> I would look for a match for column A and would return the value for Col B!
> 
> To make it more difficult, the match needs to fall within a range for
> example:
> 
> Loan amount between �50,000 - �100,000 Return X
> Loan amount between �101,000 - �150,000 Return Y
> 
> Etc
> 
> Thanks

You might be able to use one repeating field for each of LowAmount,
HighAmount and Return, but they're often a pain to use. 

You could perhaps use a separate file and some relationship link(s),
but again it's probably over-complicating things.

It's much simpler (although perhaps time consuming) to basically
recreate Excel's method set-up your fields and type in the data so that
you have:

     LowAmount1     = 50,000
     HighAmount1    = 100,000
     Return1        = X
     LowAmount2     = 101,000
     HighAmount2    = 150,000
     Return2        = Y
    etc.

These are probably best to be GLOBAL Number fields, so that their
values are only stored once for the entire database and not separately
for each record, but it depends on your database's needs.

Then your returned "lookup" could simply be calculated by a long Case
statement like this

    Return = Case(
        LoanAmount >= LowAmount1 and LoanAmount <= HighAmount1, Return1,
        LoanAmount >= LowAmount2 and LoanAmount <= HighAmount2, Return2,
        LoanAmount >= LowAmount3 and LoanAmount <= HighAmount3, Return3,
      etc.
        )

The Case statement is like one continuous If-Then-Else statement.
Filemaker simply returns whichever value it finds after the FIRST
successful test.



If the "missing" values from your example (eg. less than 50,000 or
between 100,001 and 100,999) aren't a problem, then you could leave out
the LowAmount fields altogether and simply have:

    Return = Case(
                   LoanAmount <= HighAmount1, Return1,
                   LoanAmount <= HighAmount2, Return2,
                   LoanAmount <= HighAmount3, Return3,
               etc.
                 )    

BUT you must make sure that you enter the data in INCREASING order of
value, ie. HighAmount1 < HighAmount2 < HighAmount3 < ..., for this to
work.




Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

Well do you also think that lookup next lower/higher doubious in
action??? Are you absolutely sure we're talking repeating fields here
....especially if we are shouldn't he be warned off the use of them
until everything else is exhausted??

--sd

Helpful Harry <helpful_harry@nom.de.plume.com> wrote in message news:<090520041941377805%helpful_harry@nom.de.plume.com>...
> In article <fqknc.1386$IM4.1029@nurse.blueyonder.net>, "Falcon"
> <info@kando-mortgages.com> wrote:
> 
> > Can FM 6 lookup a value based on criteria?
> > 
> > For example:
> > 
> > In a spreadsheet, I would have say 5 Cols A&B and 5 rows 1-5
> > 
> > I would look for a match for column A and would return the value for Col B!
> > 
> > To make it more difficult, the match needs to fall within a range for
> > example:
> > 
> > Loan amount between �50,000 - �100,000 Return X
> > Loan amount between �101,000 - �150,000 Return Y
> > 
> > Etc
> > 
> > Thanks
> 
> You might be able to use one repeating field for each of LowAmount,
> HighAmount and Return, but they're often a pain to use. 
> 
> You could perhaps use a separate file and some relationship link(s),
> but again it's probably over-complicating things.
> 
> It's much simpler (although perhaps time consuming) to basically
> recreate Excel's method set-up your fields and type in the data so that
> you have:
> 
>      LowAmount1     = 50,000
>      HighAmount1    = 100,000
>      Return1        = X
>      LowAmount2     = 101,000
>      HighAmount2    = 150,000
>      Return2        = Y
>     etc.
> 
> These are probably best to be GLOBAL Number fields, so that their
> values are only stored once for the entire database and not separately
> for each record, but it depends on your database's needs.
> 
> Then your returned "lookup" could simply be calculated by a long Case
> statement like this
> 
>     Return = Case(
>         LoanAmount >= LowAmount1 and LoanAmount <= HighAmount1, Return1,
>         LoanAmount >= LowAmount2 and LoanAmount <= HighAmount2, Return2,
>         LoanAmount >= LowAmount3 and LoanAmount <= HighAmount3, Return3,
>       etc.
>         )
> 
> The Case statement is like one continuous If-Then-Else statement.
> Filemaker simply returns whichever value it finds after the FIRST
> successful test.
> 
> 
> 
> If the "missing" values from your example (eg. less than 50,000 or
> between 100,001 and 100,999) aren't a problem, then you could leave out
> the LowAmount fields altogether and simply have:
> 
>     Return = Case(
>                    LoanAmount <= HighAmount1, Return1,
>                    LoanAmount <= HighAmount2, Return2,
>                    LoanAmount <= HighAmount3, Return3,
>                etc.
>                  )    
> 
> BUT you must make sure that you enter the data in INCREASING order of
> value, ie. HighAmount1 < HighAmount2 < HighAmount3 < ..., for this to
> work.
> 
> 
> 
> 
> Helpful Harry                   
> Hopefully helping harassed humans happily handle handiwork hardships  ;o)

I'm not sure if repeaters would be my first choice to store lookup 
information, but I'm having a hard time trying to figure out the pitfalls.

These repeating fields would never have a reason to be used in finds, 
reports, sorts, etc... So, what is it that automaticlly makes them the 
last resort?

It seems to me this is one time repeaters can be benefical, at least for 
reasonably small datasets.

Michael Myett

S?ren Dyhr wrote:
> Well do you also think that lookup next lower/higher doubious in
> action??? Are you absolutely sure we're talking repeating fields here
> ....especially if we are shouldn't he be warned off the use of them
> until everything else is exhausted??
> 
> --sd
> 
> Helpful Harry <helpful_harry@nom.de.plume.com> wrote in message news:<090520041941377805%helpful_harry@nom.de.plume.com>...
> 
>>In article <fqknc.1386$IM4.1029@nurse.blueyonder.net>, "Falcon"
>><info@kando-mortgages.com> wrote:
>>
>>
>>>Can FM 6 lookup a value based on criteria?
>>>
>>>For example:
>>>
>>>In a spreadsheet, I would have say 5 Cols A&B and 5 rows 1-5
>>>
>>>I would look for a match for column A and would return the value for Col B!
>>>
>>>To make it more difficult, the match needs to fall within a range for
>>>example:
>>>
>>>Loan amount between �50,000 - �100,000 Return X
>>>Loan amount between �101,000 - �150,000 Return Y
>>>
>>>Etc
>>>
>>>Thanks
>>
>>You might be able to use one repeating field for each of LowAmount,
>>HighAmount and Return, but they're often a pain to use. 
>>
>>You could perhaps use a separate file and some relationship link(s),
>>but again it's probably over-complicating things.
>>
>>It's much simpler (although perhaps time consuming) to basically
>>recreate Excel's method set-up your fields and type in the data so that
>>you have:
>>
>>     LowAmount1     = 50,000
>>     HighAmount1    = 100,000
>>     Return1        = X
>>     LowAmount2     = 101,000
>>     HighAmount2    = 150,000
>>     Return2        = Y
>>    etc.
>>
>>These are probably best to be GLOBAL Number fields, so that their
>>values are only stored once for the entire database and not separately
>>for each record, but it depends on your database's needs.
>>
>>Then your returned "lookup" could simply be calculated by a long Case
>>statement like this
>>
>>    Return = Case(
>>        LoanAmount >= LowAmount1 and LoanAmount <= HighAmount1, Return1,
>>        LoanAmount >= LowAmount2 and LoanAmount <= HighAmount2, Return2,
>>        LoanAmount >= LowAmount3 and LoanAmount <= HighAmount3, Return3,
>>      etc.
>>        )
>>
>>The Case statement is like one continuous If-Then-Else statement.
>>Filemaker simply returns whichever value it finds after the FIRST
>>successful test.
>>
>>
>>
>>If the "missing" values from your example (eg. less than 50,000 or
>>between 100,001 and 100,999) aren't a problem, then you could leave out
>>the LowAmount fields altogether and simply have:
>>
>>    Return = Case(
>>                   LoanAmount <= HighAmount1, Return1,
>>                   LoanAmount <= HighAmount2, Return2,
>>                   LoanAmount <= HighAmount3, Return3,
>>               etc.
>>                 )    
>>
>>BUT you must make sure that you enter the data in INCREASING order of
>>value, ie. HighAmount1 < HighAmount2 < HighAmount3 < ..., for this to
>>work.
>>
>>
>>
>>
>>Helpful Harry                   
>>Hopefully helping harassed humans happily handle handiwork hardships  ;o)

Michael M <mmyett@hotmail.com> wrote in message news:

> These repeating fields would never have a reason to be used in finds, 
> reports, sorts, etc... So, what is it that automaticlly makes them the 
> last resort?

But if he hits one or more of these 3 mentioned ...have I accomplished
what I wish to point out - namely that you are to paint yourself into
a corner with an unflexible datastructure, if you were to think of
repeaters as your day to day tool.

--sd

That wasn't my point at all. Just, simply that there isn't any inherent 
risk for this particular job, simple data storage for a lookup.

Any actions will be done on the looked-up data, not the source of the 
lookups.

Or are you implying repeating fields no longer have any value at all?

Michael Myett

S?ren Dyhr wrote:
> Michael M <mmyett@hotmail.com> wrote in message news:
> 
> 
>>These repeating fields would never have a reason to be used in finds, 
>>reports, sorts, etc... So, what is it that automaticlly makes them the 
>>last resort?
> 
> 
> But if he hits one or more of these 3 mentioned ...have I accomplished
> what I wish to point out - namely that you are to paint yourself into
> a corner with an unflexible datastructure, if you were to think of
> repeaters as your day to day tool.
> 
> --sd

In article <ac9976c2.0405090523.7b863233@posting.google.com>,
sdyhr@get2net.dk (S?ren Dyhr) wrote:

> Helpful Harry <helpful_harry@nom.de.plume.com> wrote in message
> news:<090520041941377805%helpful_harry@nom.de.plume.com>...
> > In article <fqknc.1386$IM4.1029@nurse.blueyonder.net>, "Falcon"
> > <info@kando-mortgages.com> wrote:
> > 
> > > Can FM 6 lookup a value based on criteria?
> > > 
> > > For example:
> > > 
> > > In a spreadsheet, I would have say 5 Cols A&B and 5 rows 1-5
> > > 
> > > I would look for a match for column A and would return the value for Col
> > > B!
> > > 
> > > To make it more difficult, the match needs to fall within a range for
> > > example:
> > > 
> > > Loan amount between �50,000 - �100,000 Return X
> > > Loan amount between �101,000 - �150,000 Return Y
> > > 
> > > Etc
> > > 
> > > Thanks
> > 
> > You might be able to use one repeating field for each of LowAmount,
> > HighAmount and Return, but they're often a pain to use. 
> > 
> > You could perhaps use a separate file and some relationship link(s),
> > but again it's probably over-complicating things.
> > 
> > It's much simpler (although perhaps time consuming) to basically
> > recreate Excel's method set-up your fields and type in the data so that
> > you have:
> > 
> >      LowAmount1     = 50,000
> >      HighAmount1    = 100,000
> >      Return1        = X
> >      LowAmount2     = 101,000
> >      HighAmount2    = 150,000
> >      Return2        = Y
> >     etc.
> > 
> > These are probably best to be GLOBAL Number fields, so that their
> > values are only stored once for the entire database and not separately
> > for each record, but it depends on your database's needs.
> > 
> > Then your returned "lookup" could simply be calculated by a long Case
> > statement like this
> > 
> >     Return = Case(
> >         LoanAmount >= LowAmount1 and LoanAmount <= HighAmount1, Return1,
> >         LoanAmount >= LowAmount2 and LoanAmount <= HighAmount2, Return2,
> >         LoanAmount >= LowAmount3 and LoanAmount <= HighAmount3, Return3,
> >       etc.
> >         )
> > 
> > The Case statement is like one continuous If-Then-Else statement.
> > Filemaker simply returns whichever value it finds after the FIRST
> > successful test.
> > 
> > 
> > 
> > If the "missing" values from your example (eg. less than 50,000 or
> > between 100,001 and 100,999) aren't a problem, then you could leave out
> > the LowAmount fields altogether and simply have:
> > 
> >     Return = Case(
> >                    LoanAmount <= HighAmount1, Return1,
> >                    LoanAmount <= HighAmount2, Return2,
> >                    LoanAmount <= HighAmount3, Return3,
> >                etc.
> >                  )    
> > 
> > BUT you must make sure that you enter the data in INCREASING order of
> > value, ie. HighAmount1 < HighAmount2 < HighAmount3 < ..., for this to
> > work.
> 
> Well do you also think that lookup next lower/higher doubious in
> action??? Are you absolutely sure we're talking repeating fields here
> ...especially if we are shouldn't he be warned off the use of them
> until everything else is exhausted??
> 
> --sd

As my first sentence says, do NOT use repeating fields - they're a pain
in almost every way.

The fields I described above as LowAmount1, LowAmount2, etc. are all
separately defined single-entry fields, not repeating ones.




Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

Michael M <mmyett@hotmail.com> wrote in message news:

> 
> Or are you implying repeating fields no longer have any value at all?

Indeed I am, almost!!! - The duck is pretty plucked especially ever
since the occurence of:

TextColor( "hello there"; WebColor( "#00FF00" ) ) 

.... when you combine it with 

Substitute((Right(10^value,value),"o","g")

Since every Microsoft Explorer is shipped with a font called webdings
and the value "g" that paints a square or more with no kerning - can
we use it with mergefields to produce graphic bars we otherwise would
reach out for repeaters to optain, entirely dynamicly created without
prestored images.

Are you abel to list other areas of the duck still is fledged????
Bearing in mind that I actually am hijacking the thread.

--sd

I can only think of (3) uses off the top of my head,

1) global image storage
2) executing multiple find requests from within a portal
3) data storage for lookups, in some cases

I agree, repeating fields have diminished in value over time, but they 
are not dead.
I also agree, if they are to be used, it should be done so carefuly.
But, isn't that the case with any features in any development tool?

With FM7, where backwards compatibility does not appear to be a issue, 
still has repeaters and with updated field behaviour.

So, it appears they still have some value to FileMaker.

Database development in FileMaker would not cease to exist without 
repeating fields, but sometimes they are handy to have around.

Michael Myett

S?ren Dyhr wrote:

> Michael M <mmyett@hotmail.com> wrote in message news:
> 
> 
>>Or are you implying repeating fields no longer have any value at all?
> 
> 
> Indeed I am, almost!!! - The duck is pretty plucked especially ever
> since the occurence of:
> 
> TextColor( "hello there"; WebColor( "#00FF00" ) ) 
> 
> .... when you combine it with 
> 
> Substitute((Right(10^value,value),"o","g")
> 
> Since every Microsoft Explorer is shipped with a font called webdings
> and the value "g" that paints a square or more with no kerning - can
> we use it with mergefields to produce graphic bars we otherwise would
> reach out for repeaters to optain, entirely dynamicly created without
> prestored images.
> 
> Are you abel to list other areas of the duck still is fledged????
> Bearing in mind that I actually am hijacking the thread.
> 
> --sd

In article <hMKnc.42507$wY.40851@nwrdny03.gnilink.net>, Michael M
<mmyett@hotmail.com> wrote:

> I can only think of (3) uses off the top of my head,
> 
> 1) global image storage
> 2) executing multiple find requests from within a portal
> 3) data storage for lookups, in some cases
> 
> I agree, repeating fields have diminished in value over time, but they 
> are not dead.
> 
> I also agree, if they are to be used, it should be done so carefuly.
> But, isn't that the case with any features in any development tool?
> 
> With FM7, where backwards compatibility does not appear to be a issue, 
> still has repeaters and with updated field behaviour.
> 
> So, it appears they still have some value to FileMaker.
> 
> Database development in FileMaker would not cease to exist without 
> repeating fields, but sometimes they are handy to have around.
> 
> Michael Myett

Repeating fields do have occasional uses, but originally they seem to
have been a half-hearted effort to give faux-related record
functionality. There's also still pieces missing in FileMaker that make
them near useless in many situations. For example, there is a
GetRepetition function, but there's no SetRepetition function that
allows you to use another field to indicate which repetition to set.
There are work arounds to some of the missing bits, but they're often
clumsy.  :o\

Personally I've never really seen any point to use a repeating field
for storing global images. To me it makes more sense when using
changeble graphics (for example) to have two fields rather than one
repeating one.
ie.
      SetField [IndicatorLight, gGreenLight]
      SetField [IndicatorLight, gRedLight]

is much easier to understand than using something like

      SetField [IndicatorLight, GetRepetition (gIndicatorImages, 1)]
      SetField [IndicatorLight, GetRepetition (gIndicatorImages, 2)]

especially if someone else has to come along later and try and figure
out what you've done.



Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)