Perform Find on related file

[MelJ]

I've spent the last 3 nights trying to find the solution for this one and have come up with ZIP!

I have FILE1 and FILE2 related by FIELD1. How do you perform a find in FILE2::FIELD1 based on the contents of FILE1:FIELD1

Thanx,

Mel

[MarkWilson]

Use the go to related record (show only related records) instead of a find.

[LaRetta]

Problems are difficult to *see* when you use File/Field. I don't believe a simple GTRR is what you want; rather that you wish to find certain Main records which have certain Related data in that field 1.

Please give us an overview and use real table/field names so that your desired outcome has some meaning. Like, "I want to find Contacts with "" in field called Category AND etc...

This information would help us decide how best to get you the results desired. The most important question is ... where do you want to end up? Do you want a list of (in this example) Contacts? Or a list of related records?

But if your find is based on the KEY (Field1?) then a straight GTRR will do it!! And you will end up on a layout based upon your related table (and all related records to THAT main Contact would be the result).

LaRetta

Edited February 15, 2006 by Guest

[MelJ]

I have a file called "Jobs" which is the file being worked with. It has a field called "Customer" (of course). This field has a drop down list based on the customer file. (Builders::Customer). Since I cannot find a way to limit input in Jobs::Customers ONLY to the drop down list....I'm certain one of the "Einsteins" I work with are going to try to enter thier own builder if they can't find it on the drop down menu. (Which I do not want to happen) So, in anticipation of this happening, I need to validate their manual entry. If their manual Customer entry does not exist in the Customer file, I can route them to the Customer file where they can fill in all the customer info before they can create a job.

I hope this is enough info for you and THANKS SOOOOO MUCH FOR YOUR HELP!

Mel

[MelJ]

Also, as a side note, I notice many of you are FSA subscribers. What is FSA?

I need to subscribe to everything I can get my grubby little hands on that will help me in this endeavor.

Thanx,

Mel

[LaRetta]

Hi Mel, here are some things to consider:

You indicate File1 (Builders) and File2 (Jobs) but you are using vs. 8. Is there a specific reason you aren’t instead using two tables within one file? You may have good reasons, but designing (even for an experienced Developer) is easier if the tables reside in one file. But you may have MEANT table instead of file. Can you clarify this for us?

You indicate a field called Customer in Jobs in which Users select from a dropdown. The value list is pulled from your Builders::Customer field. It doesn’t sound like you are using unique IDs (auto-generated serials or IDs) in your structure. If you don't use uniqueIDs, you are heading for a train wreck and I would feel obligated to wave a red flag at you. Please describe the relationship between Builders::Customer and Jobs::Customer and, forgive me if I've made incorrect assumptions. You've indicated you are fairly new and this is a prime trap for new designers.

So your dropdown contains a list of all the Builder customers. How many customers are you working with? Because your dropdown shouldn’t hold more than 20-30 items (customers). If it contains more than 40, your Users will hate you because it will cost them additional time (and frustration) to make the selection. I think there are better ways to select a customer, ie, filtered portal.

Forgive me if I get confused here; your file is named Builders – the MATCH field is called Customers and even YOU refer to it as the customer file. Is there another relationship involved (ie, more than one Customer assigned to the same Builder within the Builder file)? Or is it just one of those naming conventions you were stuck with on this solution (I understand the latter and I can adjust). But I want to make sure there isn’t an unseen connection or complication here.

You are using a dropdown LIST for your Customer selection. You are worried an ‘Einstein’ will enter their own Builder. Valid concern. Not only that, but User can DELETE a key value – the value which glues your entire solution together. It concerns me.

You would like to route them to the Customer file to create a new customer. But that probably isn’t necessary. Can one Job have more than ONE customer? Or Builder? (I hope you see my confusion here). If each Job has only ONE Customer then it would be very easy to use ‘Allow Creation of Related’ on the Builders side which, when a User is permitted to type into a Builder field, will create a new Customer automatically.

Well … I’m getting ahead of myself because much depends upon how you are structured. I’ve started a demo file to help you but until I get some clarifications on the above, I’m unsure how best to proceed. And, since we are dealing with the relational glue (match fields), it is important that the foundation be structured properly.

FSA (FileMaker Solutions Alliance) can help anyone who designs in FileMaker. Some of it is geared towards serious Developers. Only you can decide if it would be of benefit. This Forum is a great resource (open to everyone) and you might even find a Users Group. Check out FMPug and the Index here for a wealth of information available to help you with your design. Feel free to ask questions and/or provide more information, okay? There are many great people here willing to help.

LaRetta

[MelJ]

Hi Laretta,

I really appreciate your help. Answers to specific questions concerning Filemaker are difficult to find.

Let me see if I can bring you up to speed (or YOU bring ME up to speed -) )

"You indicate File1 (Builders) and File2 (Jobs) but you are using vs. 8. Is there a specific reason you aren’t instead using two tables within one file? You may have good reasons, but designing (even for an experienced Developer) is easier if the tables reside in one file. But you may have MEANT table instead of file. Can you clarify this for us?"

_____________________________________________

I'm probably using the wrong terminology. My last stint with database programming was years ago with (I'm sure you'll remember these) dBase 3+,dBase 4,5, FoxPlus, FoxPro, Clipper and the like. Although some of these were "relational" database programs, they were all "linear" in code. I am a newcomer to this "object oriented" type of programming.

Anyway, the two "files" (file1 & file2) have different file names. I thought it would keep it simpler to break it up into files instead of having one file with 100+ fields. My customer file has 30 fields. My Jobs file has 4. One customer has Many jobs, but no single job will have more than one customer. For each job that is created there are 4 fields. If I were to combine the files, each job would have 34 fields with Much duplication.(all the fields of the customer file would be filled with info for each job.) It thought that would be allot of uneccessary "overhead".

(I've seen users of this forum attach files to their posts and tried to do that with these 2 files but couldn't find a way. I probably don't have the status.)

________________________________________________

You indicate a field called Customer in Jobs in which Users select from a dropdown. The value list is pulled from your Builders::Customer field. It doesn’t sound like you are using unique IDs (auto-generated serials or IDs) in your structure. If you don't use uniqueIDs, you are heading for a train wreck and I would feel obligated to wave a red flag at you. Please describe the relationship between Builders::Customer and Jobs::Customer and, forgive me if I've made incorrect assumptions. You've indicated you are fairly new and this is a prime trap for new designers.

Each of our builders has an abbriviation that we use to identify them....."Ryan Homes" = "RY". No two builders will every have the same abbreviation. Each job also has the same.."Pendleton Plantation" = "PE". And then comes the lot #. Let's say lot 21. So the job indentifier would be RY-PE-21. And can never be duplicated because the abbreviation for the builder is unique. My Jobs layout gets the Customer and autmatically enters the abbrv into the job name via lookup in the customer file.

When the user goes to the "subdivision" field, if the subdivision exists in the jobs file, it will be on the drop down menu. If id doesn't exist, it lets them enter a new subdivision then lets them enter a new abbrv. for that subdiv.

So your dropdown contains a list of all the Builder customers. How many customers are you working with? Because your dropdown shouldn’t hold more than 20-30 items (customers). If it contains more than 40, your Users will hate you because it will cost them additional time (and frustration) to make the selection. I think there are better ways to select a customer, ie, filtered portal.

________________________________________________

We work with approx 80-90 builders. But I have auto complete enabled on the field so after they type in two or three characters it narrows the selection down to an easy pick. (If there's a better way, BELIEVE ME...I'M ALL EARS!!!)

Forgive me if I get confused here; your file is named Builders – the MATCH field is called Customers and even YOU refer to it as the customer file. Is there another relationship involved (ie, more than one Customer assigned to the same Builder within the Builder file)? Or is it just one of those naming conventions you were stuck with on this solution (I understand the latter and I can adjust). But I want to make sure there isn’t an unseen connection or complication here.

__________________________________________________

The naming of the fields was a momentary lapse in judgement. They actually are identical fields and the naming has been changed.

You are using a dropdown LIST for your Customer selection. You are worried an ‘Einstein’ will enter their own Builder. Valid concern. Not only that, but User can DELETE a key value – the value which glues your entire solution together. It concerns me.

__________________________________________________

YOU AND ME BOTH!!! I will go to then ends of the earth to "idiot proof" this thing to the limit. I plan on marketing this when I'm done with it. (of course, at the pace I'm going right now Jesus will be back before I finish). My learning curve is pretty much "flat lined" right now. I'm jujst the type that, when I run accross a stumbing block, I won't move on to something else until I figure it out. No matter how long it takes. It's something that has to be learned eventually so why not grasp it NOW!

You would like to route them to the Customer file to create a new customer. But that probably isn’t necessary. Can one Job have more than ONE customer? Or Builder? (I hope you see my confusion here). If each Job has only ONE Customer then it would be very easy to use ‘Allow Creation of Related’ on the Builders side which, when a User is permitted to type into a Builder field, will create a new Customer automatically

________________________________________________

This brings us "Full Circle". I'm afraid that a user will not take time to see that the "New Customer" they are entering already exists. That is why I'm struggling to learn how to validate the entry via the related file. I would also like to do it by a file based vailidation (define database). It would be allot more considerate to the user to let them know they have entered a duplicate builder as soon as they leave the builder field instead of waiting for them to complete 30 fields and commit the record.

I can't express in words how much I appreciate your help.

P.S. I'd be happy to send you these files if you'd like. [email protected]

[LaRetta]

Hi Mel, first I apologize for the length. But we are talking about the single MOST IMPORTANT field in your entire structure - the match key for your Customers. And I've been considering how best to approach this. You see, there are some serious flaws in your code for the UniqueID which holds your structure together. I tried very hard to devise a solution which could protect you from these flaws but I can find no way to pull it off safely. And I can not in good conscience provide you with bullets for your gun. I hope you understand.

I prepared a simple demo which can show how using a portal will protect from the problem you face but it is NOT dependent upon your unique code. It works on a system-generated ID. You should be able to adapt the portal to your ID scheme but I discourage it. If you wish to change your match fields in your existing solution to system-generated, I would be more than happy to help you through it. If not then you should still be able to adapt the portal trick to protect yourself better at least...

Why a portal over a dropdown field (with field-level validation)?

1. Field level validation will tell a User they have messed up when they exit the field. But it will NOT walk the User through the correct process.

2. We have no script-fire capability on field exit and a script is required to provide the proper walk-through to a User. You are talking about the MOST IMPORTANT field in your entire solution!!! The CustomerID will plant itself throughout your various tables and proper implementation is critical.

3. A filtered portal will grow as your business (and number of customers) grows. The existing demo first displays all customers (-All-). As more customers are added, a User can just type one, two or three letters in the filter (yellow) field to restrict the portal.

4. By insisting on an ADD BUILDER button, you maintain control. A User will have NO WAY to add a Builder unless they click it.

5. The portal is structured so that - hoping your business explodes over the next few years - a User can type 2 or 3 letters and the portal will restrict further. Users hate scrolling - whether portal or popup. If you type SM in the yellow box (for example) and then hit [enter], your portal will filter even further. Delete the contents in the yellow box and all Builders display again.

6. Popups can be changed and they change instantly - WITHOUT giving a User a chance to change their minds. What WAS that prior selection? They may have meant to open it (you know how they are) and then they didn't pay attention to the name already selected. OR ... they accidently open it and bump the delete key. Script will ask them to VERIFY their change. I have nothing against popups and use them all the time - but NOT for match or critical fields that I need to control and/or validate. Before 8 (and being Windows), I could attach scripts to popup menus and it worked a treat - but in general they aren't as reliable.

7. Since a User is required to view the portal, they are more likely to see the two Smith entries and choose the correct one. Note that I added the city in case the company names were the same.

8. You can use a global to hold the new builder name (the one asked for in the Custom Dialog). You can then add in your script - a test on patterns to see if that customer might already exist. I was too tired to add that piece. Validating for unique names is a complex subject in itself ... but you can add some basic protections nonetheless.

This selection portal doesn't need to be on the Jobs layout. It can be a popup window fired by a SELECT BUILDER button on the Jobs layout. Or the portal can be inside a tab. Since this process uses 'Allow Creation of Related' on the Builder's side, the process is quite flexible and easy. The Builder information displayed on the Jobs form can be straight merge fields so Users can't change it; regular fields (with entry disallowed); or (if they are allowed to make changes), leave the fields available. You can also require User goes elsewhere to edit the real fields (because you will be controlling it via script). Options abound. I used a Custom Dialog to allow creation of a new Builder mostly out of laziness (blush) but it also gives you more options as you work through this process. I left the Builder fields available to enter just for this demo. You wouldn't want Users typing into those fields normally because they would create a new Builder record and you wouldn't be able to control it.

General comments:

With your background, explaining relational theory won’t be necessary. I’m still unsure if you have two ACTUAL files or one file with two TABLES. In FileMaker 7/8, you can hold your files in tables (and they act just like separate files). It makes working in field definitions, graph and scripting much easier for you. Well, in this instance it won’t matter, but I want to be sure you know about it. It’s a WONDERFUL feature. When I need to go back and work in vs. 6, it drives me nuts to have to open a different Define Fields just to work. I’ve gotten lazy since 7. Yes, you can create File Reference and place the other file in the graph and create layouts based upon the other file; but your scripting becomes more difficult AND you have to open each Define Databases separately. YUK!

I am fine with your naming scheme (if you need to meet Business Rule requirements). But the unique ID which holds a database together should NEVER have meaning. This is very important and the reasons (and problems arising when not adhered to) would take pages to explain. Simply put … there are too many things that can go wrong. A few examples: Ryan Homes is set up as RY. A year later, they change their company name to Smith-Ryan Homes. Are you going to change their ID? If not, Users will assume SM (AND CREATE A NEW ONE if SM doesn't produce results). If you DO change the Builder Code, you will break your relationships. And, using that same example, what if you get two businesses with the same code? It happens more than one thinks! If you have a Builder called Brown Corporation, you would code it BR Then 6 months later, you get a new Builder called Brown Realty. What do you do? You would code it B_? But Users, if they think that code means something, will select BR for Brown Realty!! And they won't READ the Builder name which ends up displaying. Trust me. They will choose the wrong one because they will believe the codes MATTER.

You have 80-90 builders now. How many might you have 3 years from now? What if you business explodes and you have 400!! As it is, with only 2 characters, you’ve already ran into duplicate letter assignments or assigning 'non-logical' codes. Once you go ‘off’ the *code makes sense* scheme, it becomes meaningless anyway - worse ... it becomes dangerous. Then do you provide Users with a hardcopy list in which to look up the right codes? I hope you see how this can convolute and cause problems down the road. I am NOT saying do away with your coding scheme; merely that it should not be used for relational structures. Users should NEVER select the match keys and should NEVER be allowed in this field. It doesn't even need to be displayed anywhere.

And, Lord forbid, what happens if the wrong customer is assigned and it's been duplicated as part of the unique ID all through your solution. Are you going to go on a search-and-replace mission? I've been there ... it sucks. It only took once luckily for THAT lesson.

Well, I hope I gave you options. Thank you for being so thorough in responding ... it has made my task easier overall because the more logical choices stand out. And please ask more questions either on this thread or via new posts. We are all here to help.

LaRetta :wink2:

Builders.zip