VLOOKUP-equivalent relationship matches

[nylon]

Hi guys; this should be SO easy, but I can't find a solution anywhere. I need to do an inexact relationship match.

I want to look up a person's salary in a separate tax table and get the applicable tax rate. e.g.

SALARY......RATE

0-300.......0%

301-999.....5%

1000-5000...10%

etc

In Excel, it's a simple VLOOKUP, and if the exact salary is not found (as will usually be the case) the closest matching value is returned.

How do you achieve this in FMP? I have created the second table with the tax brackets and tax rates, but I can't get FMP to do anything but an exact match between the salary and tax bracket values. Even a dumb FMP lookup allows you to use the next lower/higher value if an exact match is not found - why not with relationships?

I know I could use a nasty nested IF to find the right taxrate, but I want the bracket limits to be easily editable by the user. I don't want them hardwired and hidden deep in a protected formula.

I feel dumb for asking about this - it seems such a BASIC necessity for a database, but I can't find a clue anywhere. Help please!

Mark

nylon.net

Edited October 21, 2005 by Guest

[Kent Searight]

I've created a relationship based on 2 keys from the TaxSchedule table and 1 key from the Earnings table. When the earnings field in the Earnings Table is greater than the tax bracket floor but equal to or less than the tax bracket ceiling, your tax percent is calculated in the field taxRate.

I think this example will show you what you need to know

TaxBracket.zip

Edited October 21, 2005 by Guest
I just noticed you're using version 4. The technique I explained and showed a sample of won't work for that...sorry

[nylon]

Ah, yes. Neat! Thanks for your help. The only problem is that it won't work in FMP4 in which the dB will be written. Yes, I do have FMP 5,6,7 and soon will have 8, but our school's site licence is for FMP4.

To be political for a second, I have worked with 5,6 and 7 and have yet to find a good enough reason to upgrade from v4.

Maybe v8 will be the tipping point... I know I'll have to upgrade one of these days.

I did try converting my main FMP dB (a large and complex reporting dB) to v7 but it royally messed up the file associations and I recall the built-in font smoothing (which I could not turn off) made things hard to read.

The only truly useful thing I remember seeing is the ability to construct dialog box contents from a field value... Oh, and the complex relationships in v7 seem to be handy now! Thanks for your example.

Cheers

Mark of Australia

nylon.net

[Kent Searight]

I realized you were using v4 as soon as I posted...hence the edit line at the end of my original post.

To be political for a second, I have worked with 5,6 and 7 and have yet to find a good enough reason to upgrade from v4.

Well, the ease with which I solved the problem in the example I gave you is just one of many reasons to move up to 7 or 8, in my humble opinion.

[comment]

Version 4 DOES have this capability. When you define a lookup, you have the options of "copy next lower" and "copy next higher", if no exact match.

In your example, the Rates file would look something like this:

ID......LowBoundary.....Rate

1..............0.00.......0

2............300.01.......0.05

3...........1000.00.......0.1

4...........5000.01.......0.15

Your relationship from the Salaries file is matching Salary to LowBoundary.

The Rate field in the Salaries file is looking up the next lower value from the Rate field in Rates.

[nylon]

Indeed, it may soon be time to convert. This is the first time in ten or so years that FMP4 has lacked a vital function I needed - it's done well!

Mark

[Kent Searight]

Putting my bias for version 7 aside for the moment, did you see comment's solution to your question? This should do the job for you!

[nylon]

Hi, comment. Thanks for the assist.

Yes, I knew lookup had the next lower/higher matching feature, and I got a working version using that earlier.

I was just surprised that a relationship did not have the same power. The drawback to the lookup solution - rather than the live relationship - is having to relookup whenever the lookup table changes.

In this example, a tax table is not likely to change often, but in other cases the lookup table might forever be in flux and you'd want the looked up values to always be 'fresh'.

Thanks for the help, all. Seems FMP4 is finally showing its age

Mark

[Please excuse duplicate posts - my earlier reply must be lost in the ether]

[comment]

In this example, a tax table is not likely to change often, but in other cases the lookup table might forever be in flux and you'd want the looked up values to always be 'fresh'.

I don't think it matters how often the tax table might change. The question is do you want a previously computed salary to change according to the new tax rates. I agree that there may be cases where you would want that. Although it is not as easy as in version 7 & 8, you CAN do this in version 4 as well, using techniques such as Smart Ranges.