A babe in the woods

Coolidge · August 19, 2002

Hello, I am brand new to FM, I started learning this morning. I love the program because I have come a long way toward building exactly the database that I want, but I have a couple of questions.

1. When each record is entered into the db in a table view, one of two radio options is selected. I want to add the records with one of the options checked to another layout, excluding the records with the other option checked. I have no idea how to accomplish this.

2. Each name in the db has several records associated with it. I want to display a list which includes all of the associated records at the bottom of the page so I can quickly move from one record to the individual's other records.

I have seen this implemented in address book template. It finds all people with the same last name and list them at the bottom of the page. I tried to copy this approach by creating A key field, a Global field and a multikey field, but it does not seem to work.

I am not sure if anyone can explain these issues to a complete newbie, but i would greatly appreciate it. Thanks so much.

Coolidge

LiveOak · August 19, 2002

1) Remember that layouts are just views of the records in your file. Layouts don't really "contain" records of a certain type. Any layout displays the records in the current "Found Set" either one at a time (form view) or as a list (List View). To show only records of a certain type, you must either make the records of that type the current found set OR build self-relationship (one from your file to the same file) and use a portal to display only the related records.

2) The second technique mentioned above is how you will display the other records with the same last name. Create a self-relationship with Last Name matching Last Name and display records from this relationship using a portal. When you place fields in the portal, they MUST be fields from that relationship, NOT fields of the same name in the "current file". Also, the Last Names must match EXACTLY. Any interpretation or extraneous characters in the Last Name field will break the relationship (e.g. if you add a "Jr." or "III" after the Last Name, it won't match).

-bd

CobaltSky · August 19, 2002

LiveOak's advice is spot on.

In addition, for the self-join relationship he suggests (part 2) you may wish to create a calculating field which places the last name and first name (and perhaps another field, if there is one which helps identify the individual as inique - eg middle initial or date of birth) together as a single string.

The if you use this calculated field as the basis of the relationship (ie on both sides of the field matching lists in the "Define Fields..." dialog) you will avoid a situation where different individuals with the same last names appear in each other's 'related records' listings.

Coolidge · August 19, 2002

Thanks guys, lt looks like I am going to have to break down and invest some serious time in a book of some sort. I'm going to need a lot more detail to figure this out it seems, and the manual is not working for me.

thanks again,

Coolidge

Coolidge · August 19, 2002

I did a bit more reading and solved my very remedial problem. But of course this has created a few more problems.

You mention that related fields must be exactly the same to create a match. My db contains a field with multiple keywords. I would like to display all items that share two of these keywords, ignoring an additional. Is that possible?

Also, how do you make merge field content in a portal into a link to a record?

Thanks for any advise you have,

Coolidge

CobaltSky · August 19, 2002

Hi Coolidge,

Glad you're making some progress in the woods

To get more than one match happening at the same time, you need to create what is called a 'multi-key field'. Don't faint, it's not hard. All you need to do is place each word you want to match on on a separate line in your key field (ie separated by a carriage return).

How best to do this depends on where your keywords are to begin with. If they are all in the same field separated by a comma and a space (eg "Dollar, Cent, Dime") you could convert then into a multi-key format by creating a calculation field with a result type of text and a formula along the lines of:

Substitute(KeywordField, ", ", "

Coolidge · August 19, 2002

WOW Cobalt! Thanks. I did not think it would be so easy. If you 9or anyone else) has a few more moments, I have a follow up question.

My records have three to five keywords each, I only want to display those with two or three related keywords. Can I add a function to the substitute calculation to accomplish this?

Sorry about my confusing second question. I want to the displayed related fields to act like dynamic hyperlinks so when so I can click on a related name and have it take me to the layout associated with that name.

Once again thanks so much,

Coolidge

CobaltSky · August 20, 2002

Hi Cooledge,

I know of no very simple way to pick up only those records which have two or more matching keywords. However here are a couple of things you might consider:

A: Creating calcultating multi-key fields which generate concatenated pairs of keywords in all possible combinations. This will work, however the formula will rapidly become unwieldy (not to mention outright large) if you need to cater for more than a few keywords per record in either file (eg to cater for 3 records, 9 combinations must be gernerated, but for 10, 100 must be generated).

B: If the above option is unsuitable, you could think about creating a script to generate your keyword match. The script would have to copy the keywords for the current record to a temporary field in the related file for all related records. Then within the related file, you'd need a calculation which used a formula along the lines of the following, to determine whether there are two or more keyword matches:

Case(

PatternCount(Current_keywords, Middle(Keywrds.txt, 1, Position(Keywrds.txt, ",", 1,

1) - 1)) +