Create new record based on Find results

[aslink]

I just started a new job. The data was previously maintained by a list of emails in a folder in a mail program and someone at one time had the bulk of the data in an speadsheet which has since be lost only a pdf of it is in my possession. At any rate I'm creating a db to really maintain this info. We are getting users from events so large lists of people are coming in as well as people are signing up via the web site. So while I'm keying this initial info in I'm getting live requests to add from several sources.

Problem:

I would like a script to help streamline this projects. Most of the time my db's are pretty simple and I can handle the scripting. But this is the first time in long time since I've done a db and I'm trying to use 7.

What I would like is a new button that preforms a find. Then if a match is not found create a new record with the find info pasted in so I don't have to retype it. If a match is found just show the found records.

It seems simple but I wasn't really strong in scripting ten years ago and with the new update I'm totally lost.

Help!

[transpower]

You don't need to do it that way. You can make sure that only unique names are entered, using Field | Options | Validation | Unique value. If you have a lot of John Smith's, though, you'll have to make a compound field (say firstName_lastName_street) and have the program check that that's unique.

[Ugo DI LUCA]

Hi,

Actually, you could just use relationships to see if there's a match, without any need for either a find or field validation.

A calculation would check for duplicates based on the data entered.

[Ender]

You don't need to do it that way. You can make sure that only unique names are entered, using Field | Options | Validation | Unique value. If you have a lot of John Smith's, though, you'll have to make a compound field (say firstName_lastName_street) and have the program check that that's unique.

Seems like a poor solution. You're creating a record, hoping it will be a new name. What if the name is already in there? Then you have to delete the record. What if you have more than one John Smith?

Hi aslink,

It would be better to tweak your current process, keeping it working as it does.

Go to a Find screen and find for the Contact (or whatever these records are),

If you find any, choose the correct Contact from the list,

If no match or none of the found records are right, hit a button to create a new Contact and go to the data entry layout.

This also allows you to search in Contact fields other than Name. You could search by Address, Phone Number, Date of Birth, etc. Then simply look through the list of found records and choose the correct match (or search again, or create a new record if you're sure you checked thoroughly.)

[transpower]

Don't agree, Ender. You would not have to delete the record, you would just go on to entering another in its place (because you know you already have it).

[Ender]

TP: Think about what the next step in a typical process like this would be. The user has found the name, now it's time to update the contact info and assign that contact to an event. I don't see this working well with your validation scheme.

[aslink]

Thanks for the options. I have used the unique option and don't really care for it. If it were just me using this database no problem. I'm trying to create an idiot proof solution so it doesn't take a rocket scientist or hours of training. I wanted to create a process that would work for anyone.

Click on a "New" button.

Which brings you into a layout that preforms a find on a couple of key elements.

Then click on a "Continue" button which preforms the find.

Then depending on the results.

Takes you to a list of found records

or

Creates a new record with the find data already in place so no rekeying is needed.

Like I said it seems simple just can't figure out how ot execute it.

Also as a side note when I do a find on say emails it nevers finds a match. Even thought I know it's in there. Is there a wildcard command I'm forgetting?

Thanks.

[Ender]

But why assume your users will want to enter the entire First and Last Name (oh, and Address or Social Security Number or something to avoid accidentally entering an existing record when they should be creating a new one)? FileMaker allows you to search with minimal information. It seems like unsophisticated users would like that ability ("Just search for Last Name or Phone Number.")

I think it's more important to simplify this Find process than to avoid having to retype a name. It seems this would reduce duplicate records. If they can try more than one Find with different spellings of names or different phone numbers, you won't end up with blank (abandoned) records or duplicate variations of records. Or at least not as many.

I also believe that by keeping the Find process true to the manner in which Finds work (non-invasive,) there will be less confusion working in this process and in any other Finds in you solution.

[aslink]

All good points.

But you've already assumed to much on the part of the unsophisticated user. Your assuming that someone with next to no computer time or skills will have enough presence of mind to do a multi step find. Or at least to remember to do a find everytime before creating a new record. I'm trying to remove this human error and make it as streamline and painless as possible.

One dumb temp or a good hearted volunteer can bury work for someone else to find months after they've left. I know this because I've seen it and had to fix it and it wasn't my call to leave the task with them in the first place or train them.

Needless busy work is all this prevents.

So it boils down to is this possible in FileMaker 7. If I had the money currently to purchase a new FM 7 bible and more currently script book of some sort I would. But be that as it may I thought a forum would be a great option.

I was charged with creating a new solution at one of my previous jobs. However with company lay offs I was unable to complete this same little process. Unfortunately I was working in FM 4 or 5 and all the materials I purchased from the FM Dev conference that year belonged to the company.

So here I sit trying desperately to remember things I was doing 10 to 12 years ago in at best two version of software ago and noticing that large chunks of things have changed. My hope is after a bit I'll like the new changes but currently I'm frustrated.

Don't get me wrong I have no problem discussing the pros and cons of user interface. I just don't have the time currently to debate it. I'm sure you've had tight deadlines and can understand but having a tight deadline and having to play catch up isn't any fun especially when this first project reflects on getting payed by it's out come. This position relies on me getting information out to these members for things like membership and or donations. This venture is in start up mode.

Would it help if I posted the sorry script that failed so it could be written correctly?

My intention is not to offend if I did. I know how sometimes being direct can be misread as flaming and that is not my intention. I just need to move forward with this project one way or another.

Thanks for the time spent so far.

Ken

[stanley]

Ken:

I think everyone got a bit caught up in the details of your request, that's all; it seems to me that you've got your logic down - do a find, and if a match is found, give the user a list. If they pick an item from that list, okay. Otherwise (no match found or user says no to the list) create a new record. Yes?

If you were up to FMP5, then your biggest stumbling block (in scripting) is the difference between Status() calls and Get() calls. You do your find, then in the same script you do this:

If Get(FoundCount)=0

//here you do your thing where you create a new record; send them to the right layout, etc.

Else

//here you show the list and ask them to pick something (i.e., send them to a list layout, etc.)

EndIf

Either way, you end up in Browse Mode. After the "Else" statement, you'd put the user in a layout where there's a list of found results, but also a button to (I imagine) create a new record instead.

I hope I understood you right, and this is what you're after.

-Stanley

[Ender]

I still don't like it, but this is what you wanted:

Use global fields in Browse Mode for your users to enter the find criteria, then script it to go to find mode, set the find criteria in the real fields from what's in the globals, and perform the find. If no results use your globals to set the fields in a new record.

[-Queue-]

Or create a multiple-criteria relationship from the globals to the data fields and test If [isEmpty(relationship::serial)], which, if true, means there are no related records and the data entered is new.

[aslink]

The help so far as been great, thanks.

However I'm now stuck in the year stages of writing this script. Here's what I've got so far:

Script one "New Contact p1" takes the user from one of three management layouts into a layout with globals to start the seach from.

Show Custom Dialog ["Warning"; "You are about to create a new member."]

If [Get ( LastMessageChoice ) = 1]

Go to Layout ["New Contact 1" (Contacts)]

Clear [select: Contacts::gLastname]

Go to Field [Contacts::gLastname]

Else If [Get ( LastMessageChoice ) = 2]

Go to Layout [original layout]

End If

Then once the user is in this new layout they can enter lets say the last name for now and hit the continue button to start the second script "New Contact p2".

Copy [select: Contact: gLastname]

Go to Layout ["New Contact 1 Find" (Contacts)]

Enter Find Mode []

Paste [select; Contacts::Last Name]

Set Error Capture [On]

Perform Find []

If [Get ( FoundCount ) = ""] **I've also tried 0

Go to Layout ["Data Entry" (Contacts)]

New Record/Request

Insert Calculated Result [select; Contacts:: Last Name; Contacts:: gLastname]

Else If [Get ( FoundCount ) > 0]

Go to Layout ["New Contact 1 List" (Contacts)]

End If

Most everything is working so far except the if nothing is found it dumps me in a totally different layout with 0 records found.

The hardest part of scripting for me is I'm not very good at being creative. I always seem to know just enough to get me in trouble. I know what I want as far as UI goes I just can always execute.

I feel good about being able to write the rest of the script if I can figure out how to answer the "If the Get (FoundCount)" finds nothing. Unless I've totally under estimated the script.

Thanks in advance.

Ken

[Vaughan]

"If [Get ( FoundCount ) = ""] **I've also tried 0"

It should definately be zero, not null ("").

Try using Set Field [] to generate the find request. Copy and Paste are layout dependent.

Also, make sure you're not using the "Perform Find/Replace" script step.

[iNik]

Try this sample on for size.

Basically, the script routes the user to a layout containing only global fields, each of which correlates to the find fields. In that find area is also a "Matchlevel" criteria. Look at the "IsMatched" field in the Records table to see what's up with that. Each field that matches a global field adds one to the matchlevel of a record. If that sum exceeds the global matchlevel, then the record is considered a match.

So the find command is just looking for an "IsMatched" value of 1 based on the calculated comparison with the global fields.

If it doesn't find it (returns a 401 error on the find), a new record is created and the global find values are pre-populated.

What's nice about this approach is that you can control what you're considering a match. An incorrect zip code won't have to entirely rule out a match. Additionally, by changing the calculation on the IsMatched field, you can add weight to any variables. Thus you might increase the weight on email address (if you added such a field) and decrease the weight of zip code, since they're often incorrect and change relatively frequently.

See what you think.

FindyFindyFindFind.fp7.zip