Jump to content
Sign in to follow this  
Coolidge

A babe in the woods

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Hi Coolidge,

Glad you're making some progress in the woods smile.gif

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, ", ", "

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)) +

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

1) + 2, Position(Keywrds.txt, ",", 1, 2) - Position(Keywrds.txt, ",", 1, 1) - 2)) +

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

2) + 2, Position(Keywrds.txt, ",", 1, 3) - Position(Keywrds.txt, ",", 1, 2) - 2)) +

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

3) + 2, Position(Keywrds.txt, ",", 1, 4) - Position(Keywrds.txt, ",", 1, 3) - 2)) +

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

4) + 2, Position(Keywrds.txt, ",", 1, 5) - Position(Keywrds.txt, ",", 1, 4) - 2)) +

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

5) + 2, Position(Keywrds.txt, ",", 1, 6) - Position(Keywrds.txt, ",", 1, 5) - 2))

> 1,

MatchField)


Then you need to base the relationship for your portal on this calculating field in the related file (note that the formula above only provides for up to five keywords, but can be extended as required).

In relation to your second question, getting your related fields to operate as 'go to' links is not so difficult.

In layout more, you should select a field you want to use as a 'link' and then choose "Button..." from the "Format" menu. Now locate the command "Go to Related Record" in the list of options within the Specify Button dialog and select it. Finally, select the relevant portal relationship in the menu at the right of the list (near the top). Now click OK to accept the settings and go to 'Browse Mode' to confirm that your link is working.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.