Alpha Video Training
Results 1 to 12 of 12

Thread: Re-create Item Price List for Price Level

  1. #1
    Member
    Real Name
    Elle
    Join Date
    Aug 2011
    Posts
    7

    Default Re-create Item Price List for Price Level

    Hi,

    I would like to re-create the Item Price List for Price Level report from QuickBooks (found under the Reports -> List menu) so that I can add more header information and use the report as a replacement for my Excel Price Quotes. Each Customer has a Price Level associated with their record (of fixed type - percentage increase from the Sale Price, not per item) so ideally I could run this report for each Customer and add their contact information at the top of the report (plus my logo, address details, etc).

    I'm running into a conceptual problem because there is no direct link between the Items and the Price Level. If I choose the Item table as my main table I don't have anything to link to in the Price Level table and vice versa. If I choose Customer as my main table I don't have anything to link to in the Item table.

    I get that I have to create a calculated field to create my Customer Sale Price (Item->SalesPrice x PriceLevel->PriceLevelFixedPercentage) but just getting all the data available to the report is throwing me for a loop!

    Any guidance available on where to start?

    Elle

  2. #2
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,619

    Default Re: Re-create Item Price List for Price Level

    What version of QuickBooks are you running?

    I don't think the Intuit SDK allows us to see that data.

    If so, is there a QB report that can be exported with the data in it?

    Or if you are running QB Enterprise 2011, there is a link to custom reporting that might get the proper data extracted from the QB company file.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  3. #3
    Member
    Real Name
    Elle
    Join Date
    Aug 2011
    Posts
    7

    Default Re: Re-create Item Price List for Price Level

    Hi Al,

    Thank you so much for the response. I'm using Quickbooks Premier Manufacturing and Wholesale Edition 2008. The report can be found under the Reports -> Lists -> Item Price List for Price Level menu item. When you choose that report you are then asked to select a Price Level before the report is presented to you.

    Yes, the data can be easily exported to Excel but I was hoping to avoid that sort of activity. It would be great if I could get the report to run in QReportBuilder so I can create the reports directly rather than export data over to Excel and the manipulate the layout every time.

    Basically I need to be able to cycle through the Item table, multiply the item price by the price level percentage to get the correct price for that customer (because each customer is assigned a price level). I can see these tables in QReportBuilder but don't have away to tie them together since there isn't a direct relationship.

    Any thoughts?

    Elle

  4. #4
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,619

    Default Re: Re-create Item Price List for Price Level

    Quote Originally Posted by iNetElle View Post
    I can see these tables in QReportBuilder but don't have away to tie them together since there isn't a direct relationship.
    That's what we need to figure out.. How to get that data relationship out of QB...
    Last edited by Al Buchholz; 08-22-2011 at 11:57 AM.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    Member
    Real Name
    Elle
    Join Date
    Aug 2011
    Posts
    7

    Default Re: Re-create Item Price List for Price Level

    OK, if I use the Database Explorer I can see a Set called 'item_set1' which defines the relationship between Item, Customer and Pricelevel. Can I use this set in my report? I'm not quite sure what a set is.

    Elle

  6. #6
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,619

    Default Re: Re-create Item Price List for Price Level

    Quote Originally Posted by iNetElle View Post
    OK, if I use the Database Explorer I can see a Set called 'item_set1' which defines the relationship between Item, Customer and Pricelevel. Can I use this set in my report? I'm not quite sure what a set is.

    Elle
    Ok, I see the price level table is there..

    I'm also guessing that you are looking at the control panel to see the item_set1. Right click the set and zip it. send me the zip file, please.
    Last edited by Al Buchholz; 08-22-2011 at 11:57 AM.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  7. #7
    Member
    Real Name
    Elle
    Join Date
    Aug 2011
    Posts
    7

    Default Re: Re-create Item Price List for Price Level

    There are two sets that contain the three tables I'm interested in. I've attached them both.Item_Set1.zip

  8. #8
    Member
    Real Name
    Elle
    Join Date
    Aug 2011
    Posts
    7

    Default Re: Re-create Item Price List for Price Level

    Here's the other set.Item_Set3.zip

  9. #9
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,619

    Default Re: Re-create Item Price List for Price Level

    Quote Originally Posted by iNetElle View Post
    There are two sets that contain the three tables I'm interested in. I've attached them both.Item_Set1.zip
    Refresh the pricelevelperitem table.

    Make a set like this.
    Customer => pricelevelperitem -> item.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  10. #10
    Member
    Real Name
    Elle
    Join Date
    Aug 2011
    Posts
    7

    Default Re: Re-create Item Price List for Price Level

    H Al,

    The pricelevelperitem table is only used when you've set a special price for items within the price level (i.e. item 1 is $2, item 2 is $4, etc which differs from the standard pricing of item 1 is $1.50, item 2 is $3.00, etc). When you set a a Fixed % type of price level (i.e. 20% discount on standard pricing) the only place that % value exists is the pricelevel table. Took me a while to figure that out!
    This would be easier if I was setting a special price for each item but that defeats the purpose of making price lists for customers easier to handle (because when you set a special price for each item in a per item price level the value doesn't change until you manually change it, unlike with the fixed % price level which is based on the standard sales price set in the item). Does that make sense?

    Elle

  11. #11
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,619

    Default Re: Re-create Item Price List for Price Level

    So if you don't items thenLink customer to pricelist.Don't start with item.
    Quote Originally Posted by Al Buchholz View Post
    Refresh the pricelevelperitem table.Make a set like this.Customer => pricelevelperitem -> item.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  12. #12
    Member
    Real Name
    Elle
    Join Date
    Aug 2011
    Posts
    7

    Default Re: Re-create Item Price List for Price Level

    Hi Al,

    Thank you for the help, I really appreciate the input. I think I've got it now - starting with the Customer was the way to go. Now to figure out all the other cool features in QReportBuilder!!!

    Elle

Similar Threads

  1. Report setup for price list with POR option
    By StanLisica in forum Alpha Five Version 8
    Replies: 2
    Last Post: 03-25-2008, 01:57 PM
  2. V9 price
    By donjan in forum Alpha Five Version 9 - Desktop Applications
    Replies: 0
    Last Post: 03-23-2008, 02:07 PM
  3. How can I display basic price list on every record
    By brianp in forum Alpha Five Version 6
    Replies: 4
    Last Post: 03-17-2007, 07:17 PM
  4. what's the price?
    By Birol Gezici in forum Alpha Five Version 5
    Replies: 7
    Last Post: 07-22-2002, 08:49 AM
  5. V5 Upgrade Price
    By David Day in forum Alpha Five Version 5
    Replies: 2
    Last Post: 07-19-2002, 03:39 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •