User Interface and relationships

[pcourterelle]

Whenever I design a db that requires relationships of the model sales/client where sales and client info are in separate tables I run into a user-interface problem. Which is the most efficient way to set it up so that the db user can easily access potentially 10,000 clients to enter sales info? That is, when a sale is entered into the sales db the db user must either create a new client or select from a list of current clients. From a pure db design perspective unique client ids work best but then each db user must know the id for every client. Telephone numbers work but are not stable and a single client can have multiple telephone numbers. A find function can work but the db user must perform a find client for every sale. The find can be based on name, telephone or other characteristics. For smaller dbs I've also tried using a pop-up menu that displays the name of each client and the user can select from the list.

The question, is there a better way?

thanks

phil courterelle

calgary

[Fenton]

I agree that this is a basic problem. I use a portal, filtered by the first few letters of the client's last name. I find you need about 4 letters to filter 10,000 down to about 30 possibilities. These show in the portal, sorted alphabetically; no IDs show, but that is what is set by the script. The user either clicks on a name to choose.

There is a "New" button, which takes them to a dedicated new entry layout. It is similar to the normal data entry layout, but only used during this process, because it also has a button to set the client ID into the current record of target table.

Because this layout is only for this purpose, it must be hidden normally. The user is trapped within a Loop/pause routine, so they cannot leave, without making a choice or cancelling. All buttons on the layout are set to "Halt" current script.

It is a tall narrow window, only the width needed for the names. It would be possible to show an address also, if it is necessary, on a 2nd line; but this makes the filter require more letters, as fewer rows show.

[The example file has been moved to the Sample files forum.] It would also be possible to do similar with a list view window, using a Find instead of the filter. But for people's names filters seem to work well.

[pcourterelle]

Fenton: thanks for the input, this sounds like a reasonable alternative. When you say "no IDs show, but that is what is set by the script" do you mean that the relationship is based on the client id rather than the client name? I assume this is what you mean. Having a trap built into the interface makes sense.

[Fenton]

Yes, all relationships are based on the IDs. A portal can hide the IDs, yet show any information you need to. Even duplicate names will not break it, but in that case you'd need to show more info, such as the address.

If there are often questions about the prospective client, it would be possible to use the dedicated "New" entry layout as a "more info" layout, by adding a standard > triangle button at the left of the row. That layout is like the Form view of the portal layout (which is also Form view, but looks like a list). It is still within the Loop, and hidden otherwise.

Look also at the visible scripts in the Script menu. Each has a test for Get ( AllowAbortState ) = 1, which would mean that Allow User Abort ["Off"] is NOT active. This keeps those scripts from activating during the Loop. Otherwise they would work, but the Loop would continue, leaving your client who knows where, but still stuck in the Loop. This is a version 7 function.

There is no way that I know to first Halt a currently running script from a Script menu script, and then run the rest of the script. There is no Halt current, then continue checkbox option on a Halt step (would be nice); it's only possible on a button.

[pcourterelle]

Fenton: Thanks for the downloads but unfortunately I only have FM 6 so I could not view your examples

I wasn't able to get the portal to filter properly based on the client id. What I did instead was create a calculated field in the client db that truncated the client's name to two letters. Thus Albertson became Al. I created a field in the sales db to enter a client's name and truncated it to two letters as well. I then created a portal based on the truncated names. While in the Sales db you can enter the name Allison and it will show you every name that begins with Al...Albertson, Als, Alecs and so on...the user can then select from the name. At the bottom of the portal I give the user the option to enter a brand new name into the client db if the one they are looking for does not already exist.

This is not quite what you suggested but so far it works...If the client db becomes too large I will have to change the truncation to three or four letters .

thoughts??

pc