3 Replies Latest reply on May 19, 2011 9:56 AM by philmodjunk

    Create a new Customer from Invoice automatically



      Create a new Customer from Invoice automatically


      I have a Customer Table and an Invoice Table. When I type the Customer Name into an Invoice, I want my db to lookup the customer information and add it to the invoice. That part works just fine.

      The problem is that if a customer by that name doesn't exist, I want the db to create a new record in the customer table with the new name. I have a button that does this now, but there's no way for the user to know if they need to press the button without looking the customer up manually (which somewhat defeats the point).

      I've checked the box in the relationship table saying that Invoice can create records in Customers but that didn't seem to do anything.

      Any ideas?



        • 1. Re: Create a new Customer from Invoice automatically

          What does the relationship between your Invoice and Customer table look like?

          Hopefully, you have:

          Customer::CustomerID = Invoices::CustomerID

          And you are selecting customers for an invoice with a 2 column value list that enters the CustomerID number into Invoices::CustomerID when you choose a customer. (Column 2 displays the customer name and perhaps a phone number or address.)

          I suspect from what you have in your first message, however, that you are using the customer name. That can create a lot of trouble for you when you get two customers with the same name, a customer changes their name, or you make a mistake in how the customer's name is spelled, but don't find out the error until after you have created some invoices. Basing the relationship on an ID number avoids those issues.

          A simple way to handle new customers is to add a new customer button on your layout that uses Show Custom Dialog with input fields to get the customer's name. The script then uses the input name to create a new customer record. (And this script can check for existing customers with the same name to avoid duplicates before creating the new customer record.) This script can then enter the new customer record's customer ID number in to the current Invoice record for you.

          There is also a way with scripts and script triggers to do exactly what you want with a drop down list with auto-complete enabled so that you can enter all or just the first part of a customer's name and a script trigger performs a script that uses that text to look up a customer and enters the customer ID number. If no customer of that name is found, it could create the new customer record. This method, howeve, takes a fair amount of scripting expertise.

          I have a demo file that uses this method to add lineItems to a portal that you can look at if you are enterested (you'd have to modify the scripts to fit this situation before they'd do the job). Let me know if you want to look at it and I'll post a download link in my next reply.

          • 2. Re: Create a new Customer from Invoice automatically

            It used to be Customer::CustomerID = Invoices::CustomerID. It worked exactly as you described above (with the value list etc). I was having trouble figuring out how to add new customers on the fly with this method (this has been a running theme for me since day 1).

            So... Earlier today, I changed the relationship to Customer::CustomerName=Invoices::CustomerName. I can't think of any reason I would ever want to allow duplicate customers.  I thought changing the relationship would resolve my issue (but obviously it didn't). 

            Could you post the demo you mentioned? I think going through it may be help me script a solution.


            • 3. Re: Create a new Customer from Invoice automatically

              Here it is. Please don't hesitate to ask questions about how the methods demo'd in this file work. They are set up for entering records into a portal, so the scripts used will require modification before they can be applied to what you are doing.