non exact FM7 relationships

[mattlight]

Hi guys,

Just a quick question. In FM7 you have different ways of creating a relationship as far as how it filers - either, exact or doesn't equal or anything but. How do you create a relationship where only partial information is needed like in a find request? is this possible?

what I want to achieve - Lookup a surname in the client database using a portal called Johnson Banks - in the left hand field only enter John - all records starting with John in this field in this relationship appear

Thanks

[Ender]

You can use the <, >, <=, >= operators for matching text keys too. There is a good demo of this ("Type Ahead") on databasepros.com. The problem with this technique is you need to keep First Name and Last Name in separate search fields.

Another method uses a multikey on the match side of the relationship:

The match key 'John Smith' becomes:

John

Joh

Jo

J

Smith

Smit

Smi

Sm

S

or

John Smith

John Smit

John Smi

John Sm

John S

John

John

Joh

Jo

J

So when you type 'Joh' in the search field, the relationship matches and shows the row.

There are custom functions that can easily build these multikeys for you (if you have access to Developer), or you can build them the long way, with lots of Left() functions.

I believe it's also possible to make a multikey of ALL combinations of space separated words so a search for 'Joh Smi' would match correctly, but skip the other Johns in the table. However, this might only be manageable with custom functions.

You can look for examples and discussions about "clairvoyance" and "type ahead" for more.

[-Queue-]

If you don't have Developer and are using v3, you can also use a repeating field calculation of Left( Extend(textfield); Get(CalculationRepetitionNumber) ). Create a self-relationship based on serial and a value list using selfrelationship::repeatingcalculation, including only related values starting from your base table. Then create an unstored calculation of ValueListItems( Get(FileName); "yourvaluelist" ). This will give you a dynamic key without hard-coding all the Left( )s. Just make sure the repeating field is defined to contain as many repetitions as there will be possible characters in textfield.

[mattlight]

Thanks - that makes sense. I'm quite happy to seperate the first and sur names.

I'll check it out when I get home.

[mattlight]

No, it didn't work! I understand that you are able to make a relationship that uses two fields to get a list of results that fall between between two dates by using the < and > on date fields.

I want to be able to type in a part of a surname and it bring up all that match up with this like

In the Invoicing table - Surname search type: Joh

In the Portal it displays:

Johns

Johnson

Johnston

Johanadad

etc

But, using the < than sign with the underline under it or > just makes the portal display anything it likes except things uniform to what I have typed in the search criteria field. If I type L, at least show me all those who's surname has an L in it or LI. Instead it shows all or none. Very strange

It seems that it doesn't work. Maybe if I have a repeating field of up to 30 repeats and tab between each character it will do it character by character. But that is ridiculous! It's hard enough having to fill in a form that subjects you to filling in individual boxes for your name, address,etc.

Please help! I'm using Filemaker 7.

Cheers

[mattlight]

I've downloaded the Think ahead demo to see how it is done - if it let's me see the calculations. I hope it does and that it also doesn't require a plug in for an otherwise simple task. I remember that you could do it in FileMaker 5 but it really slowed everything down so much.

[Ender]

Which method is not working for you? Can you specify the relationship and the key fields you are using.

[RalphL]

Take a look at John Mark Osborne's Type Ahead. http://www.databasepros.com

[-Queue-]

You're right, Matt. I had the relationship reversed.

What you want to do is make the repeating calculation and then relate the entry field to it. It will then update the portal when you tab. See attached for example.

Filter.zip

[Søren Dyhr]

If you don't have Developer and are using v3, you can also use a repeating field calculation of Left( Extend(textfield); Get(CalculationRepetitionNumber) ). Create a self-relationship based on serial and a value list using selfrelationship::repeatingcalculation, including only related values starting from your base table. Then create an unstored calculation of ValueListItems( Get(FileName); "yourvaluelist" ). This will give you a dynamic key without hard-coding all the Left( )s. Just make sure the repeating field is defined to contain as many repetitions as there will be possible characters in textfield.

Hmmm... There is no recursive stuff from customfunctions or anything else not monkeyclass filemaker about JMO's template!!!

--sd

[mattlight]

Thanks for all the effort on this - I really appreciate it! I will check it out when I get home.

[mattlight]

Genius!!! Thank you. I downloaded another simular solution to the problem but yours by far was the simplest way of achieving the result. Awesome! Now we can do portal relationships with partial information provided it starts off correctly and doesn't veer off somewhere. This makes it really simple for users.

Instead of flicking on through an endless scroll of clients or supplier, simply typing three letters followed by clicking on the desired result from a portal window is so simple and nice to impliment.

You've passed on a good bit of knowledge, I will add a couple of little portal/relationship gems to the PORTALS forum today which have improved the functionality of portals for me immensly. All to do with copying portal records and making entering new portal data much easier and nicer to display on screen.

[Ender]

Um, which method did you decide on?