12 Replies Latest reply on Mar 22, 2012 9:33 AM by philmodjunk

    Looking for a Directionn

    jeffd

      Title

      Looking for a Directionn

      Post

      Phil,

      In Quoter database we quote specific machines to customers. In each quote we record a stock number, make, model, description, serial number of each machine we quote. We may, and do, quote the same machine to many times but to difference customers.

      The direction I am looking for is How can I set up a layout that would show mw a history of how many time a particualr machine was quoted. I would like the layout to show one line summary for each customer with the quote date, etc of each time this machine was quoted.

      Just point me in a direction of how I can design this machine history layout.

       

      Thanks, Jeff

        • 1. Re: Looking for a Directionn
          philmodjunk

          keep in mind that I post way too many comments to be sure I am accurately recalling the design of your specific database.

          Does your database have this basic structure?

          Customers----<Quotes----<QuotedMachines>-----Machines

          If so, you can set up a summary report using a layout based on QuotedMachines.

          Can you post an example of how you want that report to look? There are a number of different ways you can group the records in QuotedMachines and the report does not have to have one line for every record--it can be set up to show one line for every group of records.

          • 2. Re: Looking for a Directionn
            jeffd

             I do have a relation of Customers - Quotes - Machines. However the Machines table consists of Make, Model, Description. The Machine table is not stock number or serial number specific. i.e. When a user selects a machine to quote in the Quoter table the Machine table only supplies the Make, Model and Description. So in Quoter the stock # and serial number of the machine is then added. I would like to create a new Table called History, then design a layout with a Portal to filter to group the specific machines by either the stock number or the serial number, this is where I'm stumped. 

            • 3. Re: Looking for a Directionn
              philmodjunk

              I don't see a need for an added table and using a portal in a report is an option that is usually best avoided. I believe you can do it from your existing table. I need to know how you want your report to look. Here are two examples of what you could create:

              Customer Fred Jones
                 Make X    Model A Serial Number 123 Times Quoted: 2
                 Make Y    Model B Serial Number 456 Times Quoted: 1

              Customer John Smith
                   Make A   Model A //and so forth

              OR

              Customer Fred Jones
                 Make X    Model A Serial Number 123 Times Quoted: 2
                      Quoted on 5/1/2011   Quoted Amount: $23,000
                      Quoted on 2/1/2012   Quoted Amount   $20,000

                 Make Y    Model B Serial Number 456 Times Quoted: 1
                      Quoted on 5/1/2010   Quoted Amount: $30,000

              Customer John Smith
                   Make A   Model A //and so forth

              In either case, you can perform finds to limit the records shown to a specific customer, specific makes and/or models and limted to specific dates or ranges of dates.

              • 4. Re: Looking for a Directionn
                jeffd

                 You're thinking along my lines now.

                What I would like to see is;

                Make - Model - Serial Number - Quoted: 5 Times

                - Date  -   Customer   -   Amount

                - Date  -   Customer   -   Amount

                - Date  -   Customer   -   Amount

                - Date  -   Customer   -   Amount

                - Date  -   Customer   -   Amount

                • 5. Re: Looking for a Directionn
                  philmodjunk

                  I was always thinking along these lines, I just needed confirmation that my thoughts matched yours. Wink

                  You don't need to add a table.

                  Repeating from a previous post, you appear to have these relationships:

                  Customers---<Quotes---<Machines

                  Don't know why you don't have an inventory table so that you can avoid repeatedly entering the same make, model, description data in Machines, but either way, this report method works.

                  In Machines, add a summary field, sQuoteCount defined as the "count of" a field that is never empty.

                  Create a layout based on Machines. In the body layout part, put your date and amount fields from machines and any desired customer fields from customers. Arrange them in a row and resize the body part by dragging the bottom boundary up to make it a narrow row. Put the Make, Model, serial number and sQuoteCount fields in the header of this layout. (If you only want to see this for one make/model at a time. If you want to list multiple make/model combinations and see the grouped data for each, let me know.)

                  To view your report make sure that you select "view as list". To see the data for a specific make and model you perform a find. This can be scripted or performed manually.

                  • 6. Re: Looking for a Directionn
                    jeffd

                     Ok, I think I understand where you are going with this. I believe you want me to create the sQuoteCount in the Quoter table, ?. The list I want to see is all quotes that have been quoted on a particular serial numbered machine. In 99% of the cases it will always be the same make model and description. 

                    For instance, say I am looking at a detailed quote, for a customer, John Doe, and the machine is a John Deere 850J Dozer with the serial number of 123456 and now I would like to click on a button that says "History", then a script would be performed to take me to the history layout with the report showing all the quotes for all customers that have been created for this 850J dozer with the serial number of 123456.

                     

                    • 7. Re: Looking for a Directionn
                      philmodjunk

                      I believe you want me to create the sQuoteCount in the Quoter table, ?

                      Create this field in Machines--not quotes. There's a fundamental difference in the original table and relationship designs that I posted and that you posted. I'm assuming that Machines in your post is the same as LineItems in my post--a record that records a single quote about a single machine. If this is not the case, let me know and disregard the following.

                      Your script performed by your button would look like this:

                      Set variable[$Serial ; value: Machines::SerialNumberField]
                      Set variable[$Model ; value: Machines::ModelNumberField]
                      Go to layout ["HistoryReport" (Machines)]
                      Enter Find mode [] //clear the pause check box
                      Set field [Machines::SerialNumberField ; $Serial ]
                      Set Field [Machines::ModelNumberField ; $Model ]
                      Set Error capture [on]
                      Perform Find []
                      Sort [restore ; no dialog]

                      • 8. Re: Looking for a Directionn
                        jeffd

                         Many Many Thanks Phil.

                        • 9. Re: Looking for a Directionn
                          jeffd

                           Phil, I would like to take this process one step further. I want to also include the history of machines of the same model and serial number in another file I have that is called Rentals. Relationship is Contacts = Quoter = Rentals. Rentals is a file we use to create rental agreements. The process you showed me above to collect the quote history works great, now I would like to include in the same report layout any rental history that the machine may have.

                          • 10. Re: Looking for a Directionn
                            philmodjunk

                            That will be a  problem.

                            You appear to have this relationship:

                            Customers---<Quoter---<Machines
                                                      |
                                                      ^
                                                    Rentals

                            If that's the relationship you have then including both records from Rentals and Machines on the same layout will be problematic. Do I have the relationships correct? If so, you may end up doing what I was earlier telling you that you didn't need to do: Export data into another table. If we merged the data from the two tables into one table, the report becomes much easier.

                            How does the Design of the Rentals table compare to Machines? Do they both have the same basic fields?

                            • 11. Re: Looking for a Directionn
                              jeffd

                               Yes, the relation you are showing is correct and the fields are very similar in the Renatsl and Quoter files. So we would need a separate table in order to merge the data from both files into one?

                              • 12. Re: Looking for a Directionn
                                philmodjunk

                                There are two options and you may want to use one as a short term solution while working towards the other option as a better long term solution.

                                Short Term Solution:

                                You can set up a table with all the fields needed for your report. A script can find the records specified in each table and use import records to import these found sets into the combined report table and sort them. A summary report like the one we have discussed here can then be set up on a layout based on this "report" table. Some developers treat this table as a "temporary" repository, deleting all records between each new report. Others, if they are confident that the exported data will not be changed after it is exported, may export all data to this table and use it as an "archive" table used for reporting purposes. The report layout works the same way, the difference is in how and when you export data to it.

                                Long Term Solution:

                                This takes a redesign of your database--something that will take more time to plan out and make happen without creating problems for the people already using the database.

                                If your two tables are very similar, consider replacing your two independent tables with a single table with an added field that identifies it as either a 'quote' or a 'rental' record. This is essentially the same 'report' table from the short term solution, but now you are entering all the data directly into this table at the moment it is first entered--eliminating the need for exporting it to another table just to produce the desired report. Multiple table occurrences, scripts, filtered portals, etc can help your layouts work much as they do now with the separate tables, but the need to make a second copy of your data has been eliminated.