Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Looking for a Directionn

Question asked by jeffd on Mar 14, 2012
Latest reply on Mar 22, 2012 by philmodjunk

Like • Show 0 Likes0
Comment • 12

Title

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

philmodjunk Mar 19, 2012 1:38 PM

Correct Answer

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]

See the reply in context

No one else had this question

Outcomes

12 Replies

philmodjunk Mar 14, 2012 2:31 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
jeffd Mar 16, 2012 7:07 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
philmodjunk Mar 16, 2012 8:52 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
jeffd Mar 16, 2012 9:50 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
philmodjunk Mar 16, 2012 10:03 AM
Mark Correct
Correct Answer
I was always thinking along these lines, I just needed confirmation that my thoughts matched yours.

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.
Like • Show 0 Likes0
Actions
jeffd Mar 19, 2012 1:22 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
philmodjunk Mar 19, 2012 1:38 PM
Unmark Correct
Correct Answer

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]
Like • Show 0 Likes0
Actions
jeffd Mar 19, 2012 2:17 PM
Mark Correct
Correct Answer
Many Many Thanks Phil.
Like • Show 0 Likes0
Actions
jeffd Mar 22, 2012 8:06 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
philmodjunk Mar 22, 2012 8:41 AM
Mark Correct
Correct Answer
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?
Like • Show 0 Likes0
Actions
jeffd Mar 22, 2012 9:17 AM
Mark Correct
Correct Answer
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?
Like • Show 0 Likes0
Actions
philmodjunk Mar 22, 2012 9:33 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions

Looking for a Directionn

Title

Post

Outcomes

Related Content

Recommended Content