Final Big FileMaker Problem to Solve

[wmugrad28]

Hello,

I've been working on a database and have one big problem left. I have two tables 'Customer' and 'Equipment' that I would like to relate to each other. A Customer can have more than one piece of equipment, but MIGHT NOT have one.

After several attempts I still can't figure out a way to create a portal on the 'Equipment' table that shows the names of the Customers from the 'Customer' table. I would like to be able to type in a few letters of a customers name into a field on the "Equipment" layout, press enter (or tab), and then get a list of all customer names that begin with those letters. Finally, I would like to be able to click on a portal record and have it auto enter into a field that would stay on the "Equipment" layout (...in the 'Equipment' table). Oh, and I prefer if the portal doesn't fill in until the user types something and presses enter/tab.

Greg

[TerenceM]

Try searching the forums for 'clairvoyance' or 'type-ahead'. The basic idea is to create a calculation field in your Customer table which has a value list of all versions of the beginning of the customer name. e.g.

Bruce

Bruc

Bru

Br

B

Create a relationship from your entry field on Equipment to this calculated field (I think it needs to be an auto-enter calculation so that it will index properly for the relationship to work). You'll find a lot more about how to work this from searching the forums.

-Terence

[Ender]

The multi-key on the match side can be a regular calc.

[wmugrad28]

Thanks guys! A link from a post from a search on clairvoyance was really helpful.

It works now, but it needs one tweak. If I make the customer search field (that I type part of the customer's name into) a global field it shows up in all of my equipment records. Which means that ALL pieces of equipment will have the same owner... which is wrong. I turned off the global option for that field (EnterCustomer). This allowed me to select a different customer name for each equipment record; however, if I create a new record OR haven't selected a customer name yet it shows the ENTIRE list of customers in the portal. How can I keep the global option off and keep the portal off until I enter something into the EnterCustomer field? I'm going to have several hundred customers and don't want to use up all the bandwidth on the network by showing a lengthy portal.

Greg

[Ender]

Use the global for the "search" field. The portal of related Customers is just the result of the search text, and you should have a process to allow (or force) users to select one of the related Customers to assign to that Equipment. In this selection script, you should set the Equipment's CustomerID (a regular text or number field,) to be the ID of the selected Customer, then clear the global search field.

[wmugrad28]

Can you simplify that a bit? Your suggesting that I go ahead and make the 'EnterCustomer' field on the Equipment table a global field. Then I'll need to create a script. That's where you lost me.

Right now I have a script running (...and it works in part how I want it to) that allows me to select a record in the portal and it automatically puts it into the 'EnterCustomer' field. Can you please explain further and/or create a quick sample? It might help me understand better. I used a technique from databasepros.com. Under resources, I typed in 'type ahead' instead of 'clairvoyance' as I said before. They have a sample file for FM 7.

Greg

[wmugrad28]

I've been working to find a solution, but I still can't make it work just right. Please take a look at my file. I've made it as small as I could.

A second issue now is on the History layout. At the bottom I have a portal for equipment owned by that certain company. If I continue to have the type of relationship between the Customer and Equipment tables, the equipment portal doesn't show; however, if I delete the two relationships and create a new one ( EnterCustomer = Customer_Name ) then it works, but the portal on the Equipment layout doesn't work anymore. How can I make both work?

Greg

Customer.zip

[Ender]

You have the search field working (though it should be defined as a global.) Now you should add a button to the Customer portal in the Equipment layout that is attached to a script that does this:

Set Field [ Customer_ID ; Customer::Customer_ID ]

For this to do what it's supposed to, you'll need to change Customer_ID in Equipment from a global to a regular number field. With this ID set, we know definitatively which customer is assigned to this piece of equipment (this is safer than using Customer Name.) However, since IDs are not very readable to users, you will need to add another Table Occurance of Customer and relate it to Equipment by Customer_ID. Then you can drop a Customer::Customer_Name field in the Equipment layout to see who the customer is for the specified ID.

So far we have a way to see who the current customer is for a piece of equipment, but to manage the history of where equipment has been will require a different design. I would recommend making your History table a join table between Customer and Equipment. Then add Start and End date fields so you can track the dates that a piece of Equipment is with each Customer. Since we're using ID's, we will need to add a few more Table Occurances to the graph to cover the various paths to the tables. I'm assuming you would want to see the History from both the Customer's perspective (What are all the things this customer has checked out and when?) and the Equipment's perspective (Who are all the customers this equipment has been checked out to and when?) For each of these, you will need to make sure you are using the correct Table Occurance as the basis for the layout, and the correct Table Occurances for the related fields in the portal. This multiple Table Occurance stuff is all about showing the data from the right perspective. See the attached TOG.

Going with this History structure, you can eliminate the Customer_ID field in Equipment, and refer solely to the History to see where a piece of equipment is at the current date (or where it was last.) Instead of setting the Customer_ID in the script above, have your script create a new record in the History table, populate the Equipment_ID, Customer_ID, and Start and (optionally) End dates.

If you're still not grokking this, I'd recommend a FileMaker 7 book or training class to help fill in the gaps.

Picture10.pdf

[wmugrad28]

Ender,

Thank you so much. I'll be studying your post today through this weekend. It's stretching my mental limits (a good thing)!

Greg

[wmugrad28]

I got the portal to work, but I'm not sure it's the same way you mentioned. I needed to create another table occurance and tie it to that. I'll give it some more thought soon.

I really want to focus on how to get the search field setup properly. It works, but if I'm going to have trouble down the road I'd like to switch.

I have two fields EnterCustomer (the field I type/partial type in) and EnterCustomerCopy (the unstored Calc field). If I change EnterCustomer to be global it has the same data in every record for EnterCustomer. If I change EnterCustomerCopy to be global (instead of being unstored) it leaves only that option to pick from in the Customer portal for the rest of the records.

Here is the script I'm using for the portal:

Set Field [PM::EnterCustomer; Customer::Customer_Name]

set Field [PM::Equipment_ID; Equipment::Equipment_ID]

Clear [select; PM::EnterCustomerCopy]

This script does not clear the EnterCustomerCopy... it flashes EnterCustomerCopy and leaves it highlighted. I'm not sure why it's not allowing me to modify it.

Greg

[Ender]

Well, you can't clear a calc field.

We are using a global for the search because it need not be stored in the record (Since the search is temporary, there is no reason to take up space in the record to store each instance of it.) We use the global search field just for the search, then when a match is found, we grab the key and stick that into our record. After that we can simply clear out the search field.

BTW: You should probably rename your search field so you can tell which one it is just from the name.