10 Replies Latest reply on Aug 20, 2012 9:19 AM by philmodjunk

    Newbee...look up customer code help



      Newbee...look up customer code help


      I am VERY new to Filemaker and am trying to write myself a database for my kids consignment business.  The difficulties I am having is that my customers and also my sellers...the same people sell and buy clothes from me.

      I have two tables, one with 'Customer Data' and one with 'Inventory Data', I have two copies of the Customer Data Table.  Within the Customer Data Table there is a 'CustomerID' and that is linked the ShopperID (first copy of table) in the Inventory Data Table and then the ConsignerID (second copy of table) in the Inventory Data Table.

      I am hoping this is what I need to do in order to run reports on my customers as 'Shopper's' or as 'Consigner's.


      Right now I am working on a layout that brings up each inventory item and I need to be able to 'SELL' it to someone.  I want to be able to type in the Last name of the customer and have their ShopperID automatically populate the Sold To field..but I cannot figure out how to do this.

      Any and all help would be appreciated...also any comments on if you think I have set up my table relationships correctly.  Like I said...I am VERY new to this and don't know much about databases but really want to learn.


      Thanks in advance!

        • 1. Re: Newbee...look up customer code help

          I think your relationships are correct. It depends on what you mean by the phrase "duplicate tables". Do you mean that you have two occurrences (boxes in Manage | database | relationships) of the same customer data table? If so, then you are off to a good start. If, on the other hand, you used the tables tab of Manage | Database, to duplicate this table, then you have a problem that needs to be fixed before tackling the issue of your post here.

          Post back here with confirmation that you have two occurrences of the same table and we can then take a look at how to do what you need for selecting customers and sellers.

          Also, do you have this type of set up for selling your goods: http://fmforums.com/forum/showpost.php?post/309136/

          • 2. Re: Newbee...look up customer code help

            Yes, sorry the terminology is still new to me...I have two occurances of the Customer Data table.  


            I'm not sure what type of set up you mean...sorry.


            When an item sells online I need to mark it as sold to the ShopperID so that later when I am putting the order together I can just look up everything that the person bought and bag their order for pick up.  I will also want to eventually email this invoice to them so they know how much money they owe when they come.

            Hope that's clearer.



            • 3. Re: Newbee...look up customer code help

              Hi Phil,


              I re-read through the thread link you provided a second time and I think I know what you are asking me now.  I don't think it's the same scenario, this is exactly what I need to do here:


              Inventory Data Table:

              Item Number

              Item Descrip

              Item Price


              Sold To (Shopper ID)


              I have a form that shows me all these fields and I don't have all the Shopper ID's memorized, so I need to be able to type the customer's last name in and have thier ShopperID auto matically fill in. (The customer's name is in the Customer Data Table but is linked to this table via the CustID)  No matter what I try I cannot get the CustomerFirst or CustomerLast to work at all on a form that is made up based on the Inventory Data Table.  If I enter the ShopperID manually, it will fill in the CustomerName automatically, but not the other way around.  I have tried a bunch of different look up's etc. but am thinking maybe I don't understand how they work exactly.


              Thanks again!!!

              • 4. Re: Newbee...look up customer code help

                I suggest that you may want an invoice just the same. If a customer came into your shop and bought 5 different items in one visit to your store, you'd create a single invoice identifying the the customer, the date of sale, etc. and then a portal would list each item sold and the price at which it was sold. Calculation fields in the Invoice table then compute the total of the items listed in the invoice and computes any applicable sales tax.

                Your Inventory table would match to the products table in the demo file you can download from that thread. It's just that in your business, you'd only sell one instance of any given product.

                Whether you use that design or not, please take a try at setting up the value list that I described. It lists ID number AND customer names so that you can select by name, but the value list enters a number. The demo file, by the way has such a value list set up and you can find it in Manage | Value lists to see how it was set up.

                • 5. Re: Newbee...look up customer code help

                  I can't download the files because they are too old of a version and I only have FM12.  I'll try again, but I kept getting error messages.  I have a value list set up right now, so a drop down list appears where I can select the customer by name and then it fill in the ShopperID, problem is that it won't autofill while I start typing in the name...so once I have this up and running with my current 1500 customer's (and hopefully many more as we grow) it would be ridiculous to seach through that list of names for each item.  Is there any way to have it "Auto-completle using existing values'...this option is greyed out for some reason and I cannot select it.

                  My business is a bit unique in that people are allowed to shop for a 14 day period.  They 'claim' items online in an auction format (so no shopping cart) and I keep a running total of what each person has 'won'.  They are then invoiced on day 12 and then they come and pick up on day 14.  Then the process starts over again with the new inventroy.  I have tried using many different invoice products in the past but none seem to work because of the unique set up of my business...hence my attempt at writing something myself.  So you can see that's why I need to assign a ShopperID to each inventory item as it's won, so then I can run a report after the auction is over to see what each person has won.  Hopefully that makes sense.  I know it's a weird situation.


                  Thank you for your time.  I appreciate more than you can know!!!


                  • 6. Re: Newbee...look up customer code help
                    1. Download the file.
                    2. Extract it from the zip file.
                    3. Launch Filemaker 12 without trying to open the file.
                    4. Select open from the file menu.
                    5. Then select this extracted file to open it. FileMaker 12 will convert the .fp7 file--the format used for versions 7 though 11 to .fmp12 format. The converted file works just fine in FileMaker 12.


                     Is there any way to have it "Auto-completle using existing values'.

                    Yes, but not with the value list we have at this time. If you review my earlier posts, I indicated that this was a "starting point" best suited for relatively small lists of values and that we could work from there to introduce more sophisticated approaches that did work better with large lists of values.

                    Auto-complete is not an option for value lists that enter a number so we need an approach where you enter part of a name and the system looks up the ID number. There will be a demo file download link at the bottom of this post. It is also in .fp7 format but you can open it from the file menu to convert it as I described at the beginning of this post. (But there's no zip archive so you don't have to extract it first.)

                    First we need a relationship from your inventory table where you select a buyer's ID to the Customer Table that will do what we need here.

                    Next, define a calculation field in the Customer Table that combines first and last names so we have a full name in one field:

                    LastNameField & ", " & FirstNameField

                    Call this field "cFullName" and be sure to select "text" as its return type. You may already have this from my earlier posts.

                    Add a text field to Inventory, named "NameLookUp".

                    Create a new Occurrence of Customer named CustomersByName and link it to Inventory like this:

                    Inventory::NameLookUp = CustomersByName::cFullName

                    On your Inventory layout, format NameLookUp as a drop down list that lists the names from cFullName in any occurrence of the Customer table.

                    In the inspector, select the auto-complete option for your drop down list.

                    Now go to Manage | Database | Fields and double click your CustomerID field in Inventory to open the field options dialog for that field. Click the auto-enter tab and select the "looked up value" option. Select the options to copy CustomerID from the CustomersByName table Occurrence.

                    Now, when you click into this field, you can start to type a person's last name and the system will auto-complete it for you and the list of possible names will filter down to just those names that match the text entered. When you select a name, the the CustomerID number from the first customer record with that full name will be copied into the CustomerID field, linking your Inventory Record to a customer record by CustomerID.

                    See if you can get this far. This is not the full answer. If you enter two people named "John Smith" in your customer table, you'll only be able to select one of the two records and the system will not tell you that there was a second John Smith in your table.


                    • 7. Re: Newbee...look up customer code help

                      This is amazing, thanks so much for the help.  Just a quick question for clarification.  I already have two occurances of the CustomerTable in order to have my consignerID linked to my CustID and subsquently my ShopperID linked to my CustID, do I need to create a third occurance for this:


                      Create a new Occurrence of Customer named CustomersByName and link it to Inventory like this:

                      Inventory::NameLookUp = CustomersByName::cFullName

                      (I did also manage to download the files last night when I too realized that I needed to just open them from FM.) 

                      • 8. Re: Newbee...look up customer code help

                        Yes, you need a third occurrence as the relationship used is different than that for your other two occurrences of this table.

                        • 9. Re: Newbee...look up customer code help

                          Hi Phil,

                          All 4 kids home from school finally found time to get back to this...it worked awesome!!!!  Thanks so much!!!  Now I need to solve the duplicate names problem...I have a lot of customers with the same name.  I am guessing you know how to do this lol!!!



                          • 10. Re: Newbee...look up customer code help

                            The method we are using requires some scripting. Are you up for some scripting?

                            The script can be performed from an OnObjectSave script trigger on your drop down list formatted field. It checks for the presence of multiple matches and pops up a list--either by changing layouts or in a floating window where you see the list of names plus data from additional fields so that you can then tell which person is the correct one to select. You then click a name in the list and the second script in this process takes the ID of the clicked contact and enters it into the ID field in the record on your original layout/window.

                            First script:

                            If [ Count ( CustomersByName::cFullName ) > 1 // You have multiple matches ]
                               Go To Related Record [Show only related records; From table: CustomersByName; Using layout: "ContactList" (Contacts)]
                            End IF

                            Second Script:

                            Set Variable [$ID ; Contacts::ContactID ]
                            Go to Layout [Inventory //specify your original layout here]
                            Set Field [Inventory::CustomerID ; $ID]
                            Commit Records

                            Set up Contact List as a list view layout with contact names, and other useful data (addresses, emails and a phone number perhaps?). Either put a button in the layout body or Select the cFullname field and use button setup to make it a button. Either way, set the button to perform the second script so that clicking a contact on the list selects it as your Customer.