12 Replies Latest reply on Feb 19, 2014 2:02 AM by BookMan

    Saving searched items to new table

    BookMan

      Title

      Saving searched items to new table

      Post

           I am new to filemaker but so far I have found it very user friendly. I however have a question for you experts. I will give you a little context.

           I go to several suppliers each day to buy books. I need to keep track of the books I buy, by recording their barcode. I want to record each suppliers barcodes in separate tables. so when I buy from MR X the records are saved in the Mr X table and then when I go to Mr Y the books I record are saved in the Mr Y table, and so on.

           I want to use the same form to save information to the different tables. is this possible perhaps using a drop down menu or something. I just cant seem to figure out how to do this!

            

        • 1. Re: Saving searched items to new table
          TKnTexas

               Not the same layout, but you can make a copy that each references the two tables.  But, I think you are losing much to do separate tables.  Just have a field on each record as to who it was purchased.  Comparison are much easier.

          • 2. Re: Saving searched items to new table
            philmodjunk

                 Using a separate table for each customer is pretty much impossible in FileMaker. Instead, you put all those records in the same table, but use a customer ID to keep Mr. X's records distinct from Mr. Y's. Adding more records is easy, adding a new table for each new customer, isn't.

                 You may also find this link on bar code scanning to be of interest if you are using a Bar Code scanner linked via USB to your computer: Need help writing script to integrate barcode scanning

                 If you plan to scan with an iPad or iPhone, the above link is not applicable.

            • 3. Re: Saving searched items to new table
              BookMan

                   Thanks to both of you. That is a much better way to do it.

                   So if I have a table of suppliers and a table of purchases. Purchases will have 2 fields; barcode and supplier. is there a way so when I am with MR Y I can have all scanned codes go against him and I don't need to input Mr Y each time I buy something? and then when I move to Mr X I can have all barcodes go against him and not have to input Mr X each time I scan a code?

              • 4. Re: Saving searched items to new table
                BookMan

                     Obviously I will relate the tables so that only a supplier from the supplier table will be able to be entered into the purchases table. it is just a matter of being able to scan barcodes and not have to select a supplier each and every time I scan.

                • 5. Re: Saving searched items to new table
                  philmodjunk

                       Take a look at the invoices starter solution that comes with your version of FileMaker. You can find an open it using the "New from Starter solution" menu option in the File menu.

                       It's set up like this:

                       Customer----<Invoices----<InvoiceData>-------Products

                       You Create a new record in Invoices and link it to a customer record and then record each item purchased in a rows of a portal to InvoiceData. The customer is only selected once for a given sales transaction.

                  • 6. Re: Saving searched items to new table
                    BookMan

                         Thanks PhilModJunk,

                         The starter solution is a little advanced for me. I really just want to be able to choose from a list of suppliers and record barcodes against them. I want to end up with a table "purchases" that has 2 fields supplier name and Barcode. I'm sure this is simple i just cant figure it out.

                          

                         I hope to one day create a database the resembles the starter solution but for the moment i am keeping it simple.

                    • 7. Re: Saving searched items to new table
                      philmodjunk

                           Yet the structure of tables and relationships is not complex and if set up in your database will do what you need. I am not suggesting that you use the invoices starter solution, only to examine it for ideas on how to set this up in simpler fashion in yours.

                           Your table of purchases can't have just those two fields as you need to also identify the client and I would assume the date when the items are purchased. (If john smith purchases 5 items in January and 2 in February, most people need to know which items were purchased in January and which were purchased in February.

                           Using the related tables allows you to record key info such as the customer's name, and the date of purchase a single time instead of over and over again for each purchased item.

                      • 8. Re: Saving searched items to new table
                        BookMan

                             I really do just need a barcode and a supplier name as this table. I would like to choose a supplier from an autocomplete drop down and have the barcode record against that supplier. Do I need to make the supplier table a value list? If so how? I will keep trying with the starter solution but I was playing with it all day yesterday and couldn't figure it out. I'm getting very frustrated with it.

                        • 9. Re: Saving searched items to new table
                          philmodjunk

                               Yet you don't want to have to re-enter the supplier's name each time you scan a new item. Doing this from the context of a related table is one way to do this.

                               Suppliers::__pkSupplierID = PurchaseLog::_fkSupplierID

                               where PurchaseLog is your table of two fields, a supplier ID and a Barcode. (No amount spent to purchase it?)

                               It's not the only option, you can set up a global field for the supplier and enter the supplier name once. Then set the supplier field in PurchaseLog to auto-enter the value of the global field. You select/enter Mr. X in the global field and then scan items in for Mr. X. When you get to Mr Y's material, enter/select Mr Y in the global field.

                               The other option is to select Mr X's Supplier record and then start scanning items. WHen it's time for Mr Y, find Mr Y's supplier record. Not much different, but I figure you'll likely need a table of suppliers anyway (if for no other reason than to record contact info about that supplier.)

                          • 10. Re: Saving searched items to new table
                            TKnTexas

                                 I am asking this as much for my understanding.  I would think at least 3 table.  The Vendor table, the master list of items that could be bought, and the table of the items that are bought.  Scanning would add a record to the last table, but it would use the item listing in the master table to reveal it all.  My understanding of barcode is that it is a product number that has been standardized within the industry.

                                 My experience was with liquor.  I had the vendor table.  I obtained a master list from one of my suppliers.  It was an industry table.  I used it to scan inventory.  I scanned the barcode, and based on my layout, it went to the next field for me to key a quantity.  But for liquor, the bar code was merely a product number.  Without the master list I would not know what the items were that were scanned.

                            • 11. Re: Saving searched items to new table
                              BookMan

                                   TKnTexas - The purchases table is a temporary table, I later obtain further information on the barcodes from a webservice and import it into a permanent table.

                                   PhilModJunk  and anyone else who knows the answer.- I am using the global field option. So I have set up a new global field and it will auto enter into the supplier field in the purchases table.

                                   Now I am sure this is something very simple that I am missing. When I try to scan a barcode into the ISBN field of the purchases table it says there are no records present and to choose the new record command. But I want to use this field to enter a new record each time something is entered into it.

                                   how do I create new records each time?

                              • 12. Re: Saving searched items to new table
                                BookMan

                                     Thanks Everyone I have solved the problem.