7 Replies Latest reply on Feb 23, 2012 10:25 AM by philmodjunk

    Biil of sale for a Car lot

    MichaelDogger

      Title

      Biil of sale for a Car lot

      Post

      I am designing a data base for a car lot. the problem I am having is that I take trade-ins, and on the bill of sale I have to enter trade in information, so I have created 2 tables. one for the inventory and one for the trade-in. Once the bill of sale is created that trade-in info has to got to my inventory table, as that's where all my reports and costs and everything is based on. a little unsure as to how to handle this, knowing that duplicating information is not always the best way to do things.  Any ideas?

       

      Thanks

      Mikdee1971

      screen_ShotAM.png

        • 1. Re: Biil of sale for a Car lot
          philmodjunk

          Why not enter the trade in data into the inventory table to start with? What problems occur if you do it that way?

          • 2. Re: Biil of sale for a Car lot
            MichaelDogger

            Well how do I get the Trade-in to create and new record?

            • 3. Re: Biil of sale for a Car lot
              MichaelDogger

              well then I still have 2 fields on the same form that are looking for a stock number from the invertory table. they always wanna display the same number. I think I need the Bill of Sale form to display the data for look up porases down the road. see who traded what for what kinda thing. I did try puting to Stck # fields on a form, but to just act like twins.

               

              Thanks Again,

              Mikdee1971

              • 4. Re: Biil of sale for a Car lot
                philmodjunk

                You can create more than one relatonship between the same two tables--one for selling the car and one for adding the trade in into inventory. To get multiple relationships between the same two tables, you create a new "table occurrence" for one of the two tables--the inventory table in this case.

                In Manage | Database | relationships, make a new table occurrence of Inventory by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

                We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                Add it to your relationships like this: (user your names for tables and fields in place of mine)

                Invoices::_fk_TradeInInvID = TradeInInventory::__pk_InventoryID

                You'd add fields from TradeInInventory--the new occurrence of the inventory table to your invoices layout in order to document the traded in vehicle.

                I'd use a script to create and link in a new record in inventory:

                Freeze Window
                Go to Layout [Inventory]
                New record/Request
                Set variable [$InvID ; value: Inventory::__pk_InventoryID ]
                Go to Layout [original layout]
                Set field [Invoices::_fk_TradeInInvID ; $InvID ]

                You can perform this script just before you start logging in the needed details about the trade in vehicle to add it into inventory.

                • 5. Re: Biil of sale for a Car lot
                  MichaelDogger

                  Wow that is really great stuff. I really appreciate the indepth answer. I am going to try that out......and I thought I was smart.....lol....Thanks again

                  • 6. Re: Biil of sale for a Car lot
                    MichaelDogger

                    OK, Sorry for my ignorance, but I have been trying to understand this and get it working. My Tables that are included on the Biil of Sale layout are as follows.

                     

                    Inventory

                    Trades

                    Customers

                     

                    I am getting confused as to what you are reffering to when you use a refereance to Invoices. I think everything else is pretty clear.

                     

                    Thanks again,

                    Mikdee

                    • 7. Re: Biil of sale for a Car lot
                      philmodjunk

                      A typical sales system has these tables:

                      Customer---<Invoices----<LineItems>----Products(Inventory)

                      Customer is one record for each customer
                      Invoices documents each time a customer buys something
                      LineItems are used to list each item purchased on an Invoice.
                      Products are one record for either each item in inventory (car sales, house sales, etc.) or each type of item in inventory (Walmart, Target, ...)

                      Unless you do fleet sales, you probably don't need a line items table as you can list just one car sold on a given bill of sale.

                      These are the actual tables found on the tables tab in Manage | Database. You can then create extra occurrences of them on the relationships tab.

                      When it comes to understanding table occurrences, this thread may help: Tutorial: What are Table Occurrences?