Jump to content
Salesforce and other SMB Solutions are coming soon. ×

perform find on existing records before adding new


ES

Recommended Posts

Hello,

I'd like to solve the following that I can't find in "the bible" nor searching for it in the forum...

In our address database before adding a new record I want to make a script that first goes through the existing records to see if the person allready exists in the database.

The search should be on three fields:

f_Organisation

f_LastName

f_Place

If the combination of Organisation and place or Last name and Place allready exists I'd like the programm to show me the found records and then give me the option of either continuing making the new record anyway or going to one of the found records...

by the way, working with FM pro 5

ok, thank you ahead

Link to comment
Share on other sites

I would advise against having your Find process automatically create a record if there's no matches. I think it's better for the user if Finds behave as Finds and don't go doing crazy things like adding records unexpectedly.

 

Instead I would start with a Find process, and if there are no records found, prompt the user to either "Find Again," "Add a Record," or "Cancel."

 

If you wish, you could store the find requests from those fields in globals, and use those to populate the fields if the user chooses to "Add a Record". I'm not sure I like this idea too much because when entering find criteria, you are allowed to be sloppy about it. Users might enter partial names or all lower case letters. This sloppy data might not get cleaned up by the user as they proceed to complete the rest of their new record.

 

If the original Find does find records, then I'd have it switch to a list layout that shows the results with a little more info about the records. Clicking on a button for a record would switch to the detail view. I'd also have buttons on the bottom of that list to "Find Again" or "Add a Record." Basically give the user plenty of opportunity to search thoroughly, then allow them to add a record.

 

This is kind of an overview of the process I have in mind. If you need more detail on how to do something, let us know specifically.

Link to comment
Share on other sites

Hi,

Thanks for your prompt reply.

I get what you're saying. The thing is i have allready a list and thought it would be handy if we don't have to go through it to see if a person/organisation is allready listed...

I guess your proposal will take the same amount of time as going through the existing list...so maybe we just have to "train" ourselves and be consequent about looking at it first.

Maybe there is another way to it:

Is it then possible to create a new record, type in name and address and once the fields i mentioned are filled and the person/organisation appears to be existing, a message comes up to say the person might allready be existing, and then have a switch to the list layout with results and a prompt wether to delete the new record (if person/organisation is the same) or continue with the new record if it is another person/organisation.

Link to comment
Share on other sites

Yes, it is possible. I would continue with Ender's suggestion to enter the data into global fields. You will need first to concatenate the info on both sides, then create a relationship based on that.

 

A calc field =

gLastName & " " & gOrganisation & " " gPlace

 

Another one =

LastName & " " & Organisation & " " Place

 

These two fields are the match fields for the relationship. Now when you enter data into the global fields, you can see any existing person/s with matching data in a portal.

 

The workflow could also be something like this:

1. Go to Layout [New Person] - this is the layout with the global fields.

2. User enters data, clicks "Enter this person".

3. The script checks:

 

If [ Related::RecordID ]

Show Message

 

Now you have option to see the related record/s (by GTTR) and make decisions.

 

Else (If there are no related records)

New Record

Set Field [ LastName ; gLastName ]

Set Field [ Organisation ; gOrganisation ]

etc.

Link to comment
Share on other sites

  • 2 weeks later...

Dear Comment and Ender,

Thank you for your advise. I finally just made a "find layout" of the address fields and have people just find on whatever field they wish, it's not "waterproof" but the users are satisfied.

Link to comment
Share on other sites

  • 11 months later...

Ender -

 

I am new to FM and really like the tool so far, but I am unable to implement your latter suggestion in this post. Could you please elaborate on how to go about this? Would this all be done via scripts? Also, you mentioned having a button for each row returned?

 

Any help would be greatly appreciated.

 

Thanks,

 

Bryan

Link to comment
Share on other sites

Sure. I have a Customer table related to an Appointment table linked by CustomerID. In order to add a new appointment I need to check if the customer already exists. If they do, then I want to use their existing information and if they don't I want to create a new customer record and then create the appointment.

 

In your post you mentioned this:

If the original Find does find records, then I'd have it switch to a list layout that shows the results with a little more info about the records. Clicking on a button for a record would switch to the detail view. I'd also have buttons on the bottom of that list to "Find Again" or "Add a Record." Basically give the user plenty of opportunity to search thoroughly, then allow them to add a record.

 

This is what I was hoping you could elaborate on.

Link to comment
Share on other sites

This is mostly done through scripting. Here's the general algorithm:

 

//Script "Find"

Enter Find Mode []

Go to Field [ Name ]

Pause Script [ Indefinitely ]

Set Error Capture [ On ]

Perform Find []

If [ get(lasterror) = 401 ]

Show Custom Dialog [ "Message" ; "No records found. Do you want to try again?" ; Buttons: "Find Again"; "Add Record"; "Cancel" ]

If [ get(currentmessagechoice) = 1 ]

Perform Script [ Find ]

Exit Script

Else If [ get(currentmessagechoice) = 2 ]

Perform Script [ New Record ]

Exit Script

Else

Go to Layout [ original layout ]

Show All Records

Exit Script

End If

End If

//Records were found

Go to Layout [ Records Found ]

 

You'll need a List View layout "Records Found" with a couple buttons to perform the Find script again, perform the New Record script, and maybe to click into a found record to go to a detail view.

Link to comment
Share on other sites

Thanks for your response. I am working on implementing your suggestions and I have run into an issue. When I get to the second If statement, If [ get(currentmessagechoice) = 1 ]

 

I receive the following error message from the Specify Calculation dialog box.

"This parameter is an invalid Get function parameter."

 

I've tried to find info about this error, but so far have been unable to correct the error. Any help would be greatly appreciated.

Link to comment
Share on other sites

Thanks for the update, that worked well. One further question. Is there a way that on the found set list layout to have a button next to each name that I could click and it would pass the CustomerID to the New Record script? That way I wouldn't have to write down the CustomerID in order to enter it on the Appointment layout.

 

Thanks for all of your help!

Link to comment
Share on other sites

There are several ways, depending on the context of the Appointment layout. It the Appointment layout is based on the same table occurence as the Records Found list, you may just need a button defined to Go to Layout [ Appointment ].

Link to comment
Share on other sites

Actually, my record results layout is based on the Customer table and contains the following fields:

CustomerID, firstName, lastName, phone1

 

My Appointments layout is based on the Appointments table and contains the following fields (in part):

AppointmentID, CustomerID, Date, ServiceID...

 

The two tables are joined by CustomerID. I was hoping that from the record results layout from the customer search that I could have a button next to each result that when clicked would take the CustomerID of that record and go to the Appointments layout and create a new record which would automatically populate with the CustomerID of the record from the record results layout.

 

I have tried several things of which this is the latest:

//Create New Appointment

Go to Layout ["Appointments"(Appointments)]

Enter Browse Mode []

New Record/Request

 

I think the problem is that the Appointments layout is serialized based on AppointmentID and I don't know how to pass the CustomerID to the Appointment layout. I'm guessing perhaps a variable or global, but am not sure.

 

Or, maybe I'm asking too much of the tool. Is this even possible in FM?

Link to comment
Share on other sites

Yes, use a variable or global field to remember the Customer ID. Then after creating the new record, set the Customer ID with the Set Field[] script step.

Link to comment
Share on other sites

Thanks again for the advice. One point of clarification. My Customer found set will include multiple customers at times. Does this mean I would need to use a variable rather than a global since global's only store one value?

 

Also, and forgive me I'm new to FM, would I create a separate field in the Customer table as a global or just convert the current CustomerID in that table to a global? I was thinking of creating a new field and setting it's value to the value from the CustomerID that is on the Customer layout. Does this make sense?

Link to comment
Share on other sites

Using a variable or a global would be the same. If using a global field, define it as a separate field since the existing Customer ID is still needed as a relational key. In this case the global field or variable would only be needed for passing information to the Appointment table.

Link to comment
Share on other sites

I created a new field called globalCustomerID in the Customer table and set it to Auto-enter Calculation and specified customerID. I was thinking this would populate the globalCustomerID with the value from customerID on the selected record. Apparently my thinking is not correct, what am I missing?

 

Thanks again.

Link to comment
Share on other sites

Setting a global is like setting any other field. It should be a scripted process, using the Set Field[] script step. Auto-enter calcs only get populated on record creation, and are not appropriate here.

Link to comment
Share on other sites

Gotcha, that makes sense. So, on my Customer Search Layout each record has a button to the right to create a new appointment for that customer. I have attached a script to that button called 'Create New Appointment'. I have tried to incorporate your suggestions into that script and here is what I have currently:

Set Field [Customer::globalCustomerID = Customer::customerID]

Go to Layout ["Appointments"(Appointments)]

Enter Browse Mode []

New Record/Request

Set Field [Appointments::customerID = Customer::globalCustomerID]

 

Unfortunately, when I get to that screen, the field never populates. Any thoughts?

Link to comment
Share on other sites

That's the right idea, but you need to choose a target field for the Set Field[] script step (hit the specify field option).

 

Remember, in a calc, a comparative operator like the '=' sign is only used as a boolean function, not to assign a value. So a calc like what you have would always evaluate to 0 unless the CustomerID happens to be the same as globalCustomerID.

Link to comment
Share on other sites

I wasn't sure about the specify field before, so thanks for letting me know. It appears though that you can only specify one field of course. So, I'm still unclear on how I can assign the value from customerID to the globalCustomerID given your statement about the = operator.

 

I've tried Get ( activefieldcontents ) but I still don't know how to say get the value from customerID and assign it to globalCustomerID. My guess is that I need to have a change of focus, I'm sorry that I'm not picking this up faster.

Link to comment
Share on other sites

In this case, setting that field just requires:

 

Set Field [Customer::globalCustomerID ; Customer::customerID]

Go to Layout ["Appointments"(Appointments)]

Enter Browse Mode []

New Record/Request

Set Field [Appointments::customerID ; Customer::globalCustomerID]

 

Where the target field is the first argument of the Set Field[] and the source field is the second (the only thing in the calc).

Link to comment
Share on other sites

Your script gets me partly there, which is great.

 

Set Field [Customer::globalCustomerID ; Customer::customerID]

Go to Layout ["Appointments"(Appointments)]

Enter Browse Mode []

New Record/Request

Set Field [Appointments::customerID ; Customer::globalCustomerID]

 

This is now populating the customerID on the customerSearch layout which is more than it was doing. However, the field Appointments::customerID on the Appointments layout still isn't being populated. I tried adding a Go to field action in the script before the Set Field but that didn't help either.

 

I was under the assumption that global fields could be accessed from one layout to the other and that they persist for the session, so I wouldn't think this is a case that the global field value is getting wiped out. What say you?

Link to comment
Share on other sites

IT WORKS!!! Thank you so much! Through all the iterations I must have turned off the global storage option. Re-enabled it and it works as you described!

 

Thank you very much for your help.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...

Important Information

Terms of Use