14 Replies Latest reply on Jul 5, 2013 2:43 AM by MarkPage

# Finding an inexact result

### Title

Finding an inexact result

### Post

 Hi,                                                               I have a simple table with 4 fields. Species; Age; Height; and Yield Class                                                               I want to find the yield class, for a given Species Age and Height.                                                               I have it working if exact numbers are put in for the three fields. Species and age are definitive, but the height I find in the forest may not be represented in the database. For example I may measure a tree and record 21.7m. However in the database 21.7 may not exist - the lower figure may be 20.5 (with a Yield Class of 6) and the next higher figure may be 22.0 (with a Yield Class of 8) In this case the query does not give a result, but I need it to return the lower Yield Class (6 in this case).                                                               Can any one advise how I can get my inexact result, please?                                                               I suspect it's simple, but then I'm a simple guy.                                                               Cheers                                                               Mark

• ###### 1. Re: Finding an inexact result

If I understand correctly....

You are trying to find the Yield Class of the Height range of the tallest qualifing Height range, without being ≥ the next higher Range.   Another way of saying that is Within Range.

and another way to express that  mathmaticaly is   R2 > X ≥ R1 , where R1 to Rn are heights sorted such that R2>R1 and X is  your search Height.

If true then i would...

1) create a New Global Numer field and call it... say... gActualHeight

2) Create a new Table of Occuance relating that global to your Simple table HeightField with a[ not the default = ].

3) Create a one row portal with that new TO, that is sorted Highest to lowest.  That portal put in a new Entry Layout allowing gActualHeight to be entered by the user

4) Enter the value and the desire results appears in the portal.

I hope i understood the issue

Jim...

• ###### 2. Re: Finding an inexact result

Hi Jim,

Yes, this is what I am trying to do.

I confess I am just starting to convert to FMP. I've spent a lot of time on Lotus Approach, and even more on MS Access. SO I need pointed to how to create queries/finds in the FMP language.

Cheers

Mark

• ###### 3. Re: Finding an inexact result

Take a look at this thread showing scripted find examples: Scripted Find Examples

And look for the example that finds records with a range of dates. It could be modified to find on a range of Heights.

• ###### 4. Re: Finding an inexact result

I am not familiar with those programs at all.    Phil's link will help with a Find and script method.  There are many ways to solve the Within range search.  My suggestion avoided the Find and script way.  You can even use Sort to do it.  Consultants, like Phil and not me, would probably set you up and coach for a very small fee.

You will find this forum a great place to come for Tips and Tricks, some place to break a mental LOG JAMB, and pointers to places of knowlede.

Read a bit of the User Manual on Relationships and field types and use Knowledge base for FMP tips to.  All databases are similar.  Send Phil an Mail note to check him out.

Look for key words in this forum, like global, Table of Occurance, Portals, Scripts, Find. etc. in your Help.

Jim...

• ###### 5. Re: Finding an inexact result

@Phil  Here is another classic function to add to a group.  WITHIN or INSIDE or INCLUDED or HiLo or UpTo or DownTo, and make them accept (data) to deal with all formats.

Jim...

• ###### 6. Re: Finding an inexact result

Jim,

Thanks for your help (and Phil, too).

I'll check out what you've suggested, and see how I get on.

Got to say - this seems like a friendly forum!

Cheers

Mark

• ###### 7. Re: Finding an inexact result

I learn a lot here also.   It is very hard to design a DB in a chat environment or even know the extent of knowlege the poster has.  I just spent \$50 on some reference books and saved time.  I knew about 80% of the material but how much is it worth to save a day or two of your time to get your DB on the right path.

A Consultant to design and support a database may be more than you can afford, but some coaching and guidance should be affordable.

Dive in and please come back with questions.

I am in a thread right now that is EXACTLY my next step in a DB I am working on, so the solution will save me time.

Jim...

• ###### 8. Re: Finding an inexact result

Hi Jim

I've created the YC table, and it holds the 4 fields Species; Age; Height; and Yield Class. My data collection table contains those three fields and I've related them to Species Age and Height in the YC table, and placed in my data collection table the YC field. I've chosen the 'less than or equal to' link.

It brings over a YC perfectly, but always chooses the YC lower than the one it should choose.

I'm a bit baffled by this. Any help would be welcomed.

Also I'm not sure I quite understand the solution you suggested in your initial reply to me:

1) create a New Global Numer field and call it... say... gActualHeight (Query: where do I put this? How do I relate it to what you put below?)

2) Create a new Table of Occurance relating that global to your Simple table HeightField with a  [ not the default = ].

3) Create a one row portal with that new TO, that is sorted Highest to lowest.  That portal put in a new Entry Layout allowing gActualHeight to be entered by the user

4) Enter the value and the desire results appears in the portal.

Cheers

Mark

• ###### 9. Re: Finding an inexact result

i appreciate with jim Gurley because he has cleared the discusion logically.

• ###### 10. Re: Finding an inexact result

well we now have 2 tables.  It sounds like you have a FieldData  and YC tables. If I am correct, you won't need gActualHeight. It appears that your YC table is Quick Refernce for the Species, Age, Height to give you  YC.    Rather than lead you wrong,  Pleas respond back

Jim...

• ###### 11. Re: Finding an inexact result

Jim,

Thats exactly what I have.

I've posted the relationships table here as well so you can see.

I'm making a lot of progress with FMP - I think it bears some similarities to Lotus Approach - but this little issue is baffling me. It's crucial that I get it right.

• ###### 12. Re: Finding an inexact result

Hi Jim,

I thought I could post two screenshots, but apparently not. The one in the previous post is the YC table.

If I put in the field collection data table: EL; 10; 4.5; the answer I need is 6, but I keep getting the answer 8.

I've attached the relationships screenshot to this post.

Cheers

Mark

• ###### 13. Re: Finding an inexact result

OK  No need for gActualHeight  .   I will assume your Data Entry Layout is for ForrestiPad  and YC dBase is your table of to get the Within range data.

1) create a one row portal on the Entry Layout using the 3 field relastionship  and sort your portal based on YC dBase::HT  in decending order [high to low]

the relationships should be ForrestiPad::HT  ≥ YC dBase::HT    AND

2) Enter data and when the AGE and SPP and HT of ≥ Heightavailable without exceeding should appear in y our new portal.

Jim...

• ###### 14. Re: Finding an inexact result

Hi Jim,

This is perfect!

Thanks for taking the trouble in replying to me. Clearly I need to spend a bit more time in fathoming out Portals.

Much appreciated.

Cheers

Mark