Question about auto serial field

[Chrissy10]

Newbie here need help please

I set a job ticket up in fmpro advanced 11

I have several sales people in my office and want to set my job number field so that when the sales rep picks their name from the “sales rep field” it will auto enter their next job number. Each rep has their own series of numbers. I would like to do this so we do not end up with duplicate job numbers.

My question is: Can this be done and can someone lead me in the right direction.

Thank You in Advance for your help

[Digital Life]

Newbie here need help please

I set a job ticket up in fmpro advanced 11

I have several sales people in my office and want to set my job number field so that when the sales rep picks their name from the “sales rep field” it will auto enter their next job number. Each rep has their own series of numbers. I would like to do this so we do not end up with duplicate job numbers.

My question is: Can this be done and can someone lead me in the right direction.

Thank You in Advance for your help

You need to create a table for the sales reps. Suggested fields might be: RepID, FirstName, LastName, NextJobNumber

If you don't want two names then just make a field called name.

Then when you issue a new job ticket the script would see who the sales rep is, go to that rep in the sales rep table, set the NextJobNumber field to itself to lock the record, set $TicketNumber (a variable) to the NextJobNumber value, then set NextJobNumber to NextJobNumber+1, go back to the job ticket layout and set the job ticket number to $TicketNumber. Try that.

[bcooney]

"I want to set my job number field so that when the sales rep picks their name from the “sales rep field” it will auto enter their next job number. "

This doesn't sound good. I have questions about your workflow.

In order for a salesperson to have been assigned job numbers, those job records need to pre-exist. Do they? You really never want to calculate ID fields, but rather use an auto-enter serial number to uniquely identify each record (this is populated as the record is created).

So, I see this as how to associate existing jobs with sales rep(s). If a job is only assigned to one rep, then you need a RepID in the Jobs table. If a job is assigned to more than one rep, then you need a table btw Jobs and Reps, a join table, "Assignments" perhaps.

You can put a portal on a Rep form to all the Jobs to which this rep is related (assigned). They can, if needed, mark this record as "taken."

[Chrissy10]

sorry for not giving more information before

what I have is this:

I have a customer table where the rep would enter new customer data in. There is a field for sales rep "a popup" with all the sales reps in the company.

Then a table for job ticket - on the actual layout(order form) is the field for sales rep which pulls the sales rep info from the customer table "sales rep" field.

On the Job Ticket itself is a field for Job Number. Each sales rep is assigned a specific set of job numbers ie: 4000, 5000, 6000 etc. what I would like is

that when the rep creates a new job ticket to auto-enter their next job number into the job number field. A job is never assigned to more than one rep. I also have a portal setup to pull all the customer jobs to their card.

I hope this helps explain more thanks again.

[bcooney]

Job numbers should be auto-entered when they are created and not derived. Assigning SalesReps specific sets of job numbers serves no purpose. You can easily tell which sales rep is assigned to Jobs by looking at the Job::SalesRepID (which is looked up from the Customer table).

Jobs.fp7.zip

[Digital Life]

I agree with bcooney that consecutive job numbers are probably the best. But i've seen lots of businesses with historical practices of using job ranges which allow other staff to tell at a glance by the number who the rep is even where the name is not explicitly shown. So I've attached a file based on bcooney's file and modified, to show how to do this.

You will run into a mess though if you get to the end of a range and start to flow into another reps already assigned numbers.

Sorry I can't seem to upload the file. It keeps saying "You're not allowed to upload a file of that type". It's a filemaker 11 file???

[Digital Life]

Sorry here's the mofied file:Jobs2.fp7.zip

[Russell Barlow]

I agree with bcooney that consecutive job numbers are probably the best. But i've seen lots of businesses with historical practices of using job ranges which allow other staff to tell at a glance by the number who the rep is even where the name is not explicitly shown. So I've attached a file based on bcooney's file and modified, to show how to do this.

You will run into a mess though if you get to the end of a range and start to flow into another reps already assigned numbers.

Sorry I can't seem to upload the file. It keeps saying "You're not allowed to upload a file of that type". It's a filemaker 11 file???

Creating a calculated field that takes the serialized job number and combines it with case statement that iterates through the sales people for a correct code. So, if I generated a new Sales Order and its ID is 12122 and I'm the sales person. You could do something like 12122-RB, RB-12122, RB12122, etc.

[Chrissy10]

I do agree as well and wish I could use just the next auto-entered serial field - I understand what you are saying bcooney however, assigning specific job numbers for each of our sales reps does serve a great purpose in our company. The job number and customer id as Digital Life said "But i've seen lots of businesses with historical practices of using job ranges which allow other staff to tell at a glance by the number who the rep is even where the name is not explicitly shown." It is not only to see at a glance who the rep is on reports but the specific job number also plays a part for our accounting department. I appreciate all that everyone has contributed since you are alot more experienced than I am. Thank you for the filemaker file I am going to see if I can modify it to meet my needs will let you know how it goes.

[Vaughan]

So what happens to the job number if the sales rep leaves and the job is assigned to another rep: change the job number or have the wrong rep in the code? I can see problems with both of these options.