Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Need help : User Logs into a blank data entry form to create new record only

Question asked by KirinIchiban on Mar 22, 2011
Latest reply on Mar 23, 2011 by philmodjunk

Like • Show 0 Likes0
Comment • 3

Title

Post

I am a beginner with FMP 11.

I've done hours of searching for a couple days, while at the same time fiddling around with the actual FMP database itself. But I can't seem to grasp the concept of what I'm finding to figure out how I would apply it to my situation.

I'll try to keep this simple. I have a Customers table, and a Sales Rep table. There is a SalesRep field in the Customers table that I use to relate the two tables. I've created accounts for each Sales Rep to access the database (but the account name is not the actual Sales rep name). I know it has something to do with defining custom privilege sets, but I can't seem to get it right. What I want is that when a Sales Rep logs into the database, that he/she is brought to a blank New Customer layout form that is prepopulated with the Sales rep's information such as id, name, phone number, etc. I want it so this new customer layout form is the only layout/table/form that Sales reps have access to. The sales rep would simply fill out any new customer information and commit the record. But I want it so that they cannot view or access any other Customer record. The purpose of the form would be for a Sales Rep to create new customer records only, and have that new customer record related to the Sales Rep whose account is loggin in.

Can someone please explain it simply enough so that I can understand the concepts I need to make this happen, and then explain it again in specific details so that I can try to walk through it step by step?

No one else has this question

Outcomes

3 Replies

philmodjunk Mar 22, 2011 9:07 AM
Mark Correct
Correct Answer
First a little background reading should help. If you have not already done so, please see "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis".

In this case, we need a lock expression that only enables the sales rep to see the current record only.

What is not clear to me is why you don't want the sales rep to see records they have entered previously, but that's a function of your business practices more than the database design. You might, however, need a way for a SalesRep to be able to re-open and edit their most recently created record if they discover that they've made a mistake only after they have committed the record.

There are several ways you can set a flag that limits them to the current, newly created record only. I'm going to use a method that allows you to unlock a record for salesRep access should you discover that you need to in order to enable the SalesRep to correct errors.

If you don't have a field like this in your table already, define an auto-entered serial number field to uniquely identify each record. I'll call it CustomerID for the purpose of this example. Define a global number field, gCurrentCustID. Now use this record access formula to control access:

( CustomerID = gCurrentCustID )

If there are non sales rep users that do need access, you can change it to:

( CustomerID = gCurrentCustID ) or ( get ( AccountPrivilegeSetName ) = "PrivilegeSetNameInQuotes" )

When the Sales Rep clicks the button to access this layout for new customers, run this script:

New Record/Request
Set Field [Customers::gCurrentCustID ; Customers::CustomerID]

That creates a new record with access permitted.

Add a Save Button that does this script to lock the record when the SalesRep has completed the record:

Set Field [Customers::gCurrentcustID ; "" ]

If you need to unlock a record for SalesRep access, you can write a script that asks for a manager password in a Show Custom Dialog and then uses set field to assign the current customerID value back to the global gCurrentCustID field.
Like • Show 0 Likes0
Actions
KirinIchiban Mar 22, 2011 7:13 PM
Mark Correct
Correct Answer
Thank you so much for responding.

(only after writing the following long explanation), this statement more or less cuts to the point, and is the meat of the matter: Yes that is my decision, sales rep accounts should not be able to edit existing data, only enter new customer records, or else select existing customers from a drop down list, and enter new follow up notes concerning the customer.

Yes Phil your suggestion with being able to see their past records is what I would like also, I was afraid of asking for too much in one question and getting everyone confused, which I most likely will do with this post... To be honest, I haven't figured out totally what it is that I need or will work for me. Yes I've done a little background reading, for over a week, watched Lynda videos, etc, but alas, I still seem to be at a loss with more than superfcial database subjects...However I realize the power of FMP and how it could, if only I knew how, do almost everything that I need to manage my business (I am mostly Mac but must work with Windows, and a Quickbooks PC (because Mac version sucks) convert), and am slowly trying to figure out FMP to do all things from inventory control, purchase orders, sales orders, customer and employee management, and money flow and accounts management, what makes it even more complicated is money is dealt in 2 different currencies.

But right now I am trying to focus on the customers table and getting that locked down and solidly in place. Ok, let's start with this... I have customers that are company customers, customers that the company generates through paying for market research, events, and advertisements, etc. and added into the table, these will be "open" customers, customers that have been created by company, but unassigned to a sales rep. Then there are customers that have been created or "found" by sales rep, but unnassigned to any sales rep since there have been no sales, and those that have been assigned to a sales rep, because of an actual order. What happens is that a sales rep can earn a percentage of the first order only, for finding new customers. Once a sales order has been made, that customer is also "assigned" to the sales rep that made the order... So I actually have 2 fields to represent this: CustomerCreatedby & CustomerSalesRep. On an auto updated basis, I want for sales reps to be able to see all the customers that have been assigned to them and company customers that are "open" or CustomerSalesRep is empty, for them to cold call, and all customers that they have created, but has not been assigned to any sales rep due to the fact there has not been an actual order. Further complicating things is that I eventually want to have a time limit set on how long a newly created customer remains "unnassigned" before it is assigned as a company customer, or "open" for all sales reps to view and call. This way, even if the sales rep that created the customer cannot make a sale, perhaps another sales rep can, but the creating sales rep can still earn a % commision on the first sale only. This time limit would apply to any assigned sales rep customer as well, say after 6 months since the last sale, if the sales rep hasn't made another sale, then that customer will be reassigned to the company customers & become "open" for all to see again. The point is I don't want sales reps to be able to see and cold call customers that have been assigned to another sales rep, and customers that have been "recently" found by another sales rep.

The real difficult problem that I don't think even FMP will be able to avoid, is how to keep Sales Reps from adding what they think are new customers but actually already existing customers into the table. 3 different sales reps can have the same customer, but enter them in 3 different vratiations, and then I would basically have 3 different records for one customer which I would have to manually sift through and evaluate wether or not they are the same customer....any suggestions?

Ideally what I would like is... When a user (sales rep) logs into their account, what they see is a blank form. This form would be prepopulated with the sales rep's name and information and any commiting of the record would automatically relate to the logged in user. The other fields would be for customer information. I think it might be best that the sales rep will have to press "new record" before filling out any information, rather than instantly being inserted into a new record form (perhaps to lessen the chances that the customer table isn't populated with blank records and user mistakes, i don't know).

The sales rep would either start filling out the form and commit the record, which would create a new customer record (Createdby (sales rep name), but SalesRep (unassigned)). Or else select all customers that are they are allowed to view through a drop down box. These customers are as explained above, any "open" customers, and any customers that they have recently added to the table, but that has not yet been assigned to any other sales rep since there has been no actual sales order.

What I'm not sure I need is wether or not I want them to be able to edit existing data. Say they select an open company customer, do I really want them to edit the customer's phone number and address....or do I just want them to add new notes to relate to the customer? I think the latter. Any editing of existing data should be for admin accounts only. Yes that is my decision, sales rep accounts should not be able to edit existing data, only enter new customer records, or else select existing customers from a drop down list, and enter new follow up notes concerning the customer.

The last but very integral bit.... I need to have a CustomerNotes table I presume.. so on the form, either when creating a new customer/record or selecting an existing customer from the drop down list, there needs to be a field or portal for customer notes that will append or create a new CustomerNotes record every time anyone inputs a new note, this CustomerNotes record will of course be auto assigned the user account name that entered the note, the time stamp they entered the note, the Sales rep name (I guess from a lookup of the account name?), is related to the customer via a matching field, and the actually note/comment itself made by the sales rep. What I want is that they can view old notes made by themselves or other sales reps, but only be able to add new notes, not delete or edit exisiting notes (to prevent sales reps from sabotaging each other and the integrity of company data). I was actually able to do the portal of the CustomerNotes table inside a form layout on the Customers table. But what I have to figure out is how to prevent the logged in user from editing, and deleting existing data and notes.

Thank you very very much if you've taken the time to read and try to understand my solution problem. And I would be indebted to anyone that could explain to me the concepts simple enough for me to understand, and then a more detailed step by step for me to be able to follow and try to develop this myself.

THANK YOU
Like • Show 0 Likes0
Actions
philmodjunk Mar 23, 2011 9:27 AM
Mark Correct
Correct Answer
I'd put the notes in a related table so that multiple notes records can be entered for a given customer. You have several issues here and I've one only read the bold face entries. Time for this forum is limited today....

I'd look at collecting data in global fields. When the user clicks a button to save the new data, your script can check for existing customers, add a new note record etc. This allows them to add new data in the global fields, but prevents direct access to past entries.

Eliminating duplicate entries for the same customer can be impossible to do perfectly. People have the same first last names. People with the same first last names can have the same address and home phone numbers (John Smith Jr. and John Smith Sr.). People change addresses, phone numbers between one visit to your business and the next and you won't know of the change....

Best you can do is script a find operations that searches for possible duplicates and then present them in a list where the user can determine, with the help of the customer, whether they already have a record in your system.
Like • Show 0 Likes0
Actions

Need help : User Logs into a blank data entry form to create new record only

Title

Post

Outcomes

Related Content

Recommended Content