Creating Quotes based on inventory lines

[briandye]

I think I should give an overview of what I am trying to accomplish.

I have an inventory table that has maybe 350 sku's. These skus are a part of a product line, maybe 3 or so different SKUs for different colors of products that are in the same product line.

I am attempting to make a quoting module that asks for the customer name, to quote this to, and then display a list of ALL of the Product Lines, prompting the user to choose yes or no for each line, if they mark yes then the SKUs that belong to that product line will be included in the PDF that is generated.

I can't seam to display the list of product lines as a portal. Is there a recommended way I should do this and allow the users to select yes or no for each product line? Any help would be much appreciated. If anyone has any questions please let me know.

[briandye]

After thinking about it a little further, I guess I could setup a pull down value list displaying all the product lines and use an Add button performing a script taking the product line code and adding it to the quotable products...

I was just looking for something a little more user friendly.

[Fenton]

First, since this is a "quote," can we assume that there is a quote line items table to hold these records?

As far as showing the 300 SKUs, there's more than one way to do it. But choosing one from a drop-down list, then hitting an "Add" button, would not be my first choice, if I was a user.

Also, since you've got 3 or so different SKUs for 1 product, for colors, one would like to display just the one product, and let them pick the color.

Questions:

1. Does the user really need to see the SKUs to pick the product? Could they not tell by the name?

2.a. Is there a Product table (without the color)?

2.b. Is the SKU of the final Product wColor based on the SKU of the Product?

3. If not, is there a way to positively group a "product" (all colors), with an indexed field?

What I'm thinking is a portal showing just the Products (not for any particular color), then, in the portal row, a radio button choice for the particular color desired (displayed horizontally; how many colors are we talking about here?).

That would be a global field, its value list filtered for that product (if different products have different colors available).

After choosing the color desired, you can click a button in the portal to create a record in the quote line items with the SKU matching the Product AND gColor.

Something like that. But we don't even know whether you've got 1 Product table, or 2, what the SKUs look like, whether a product with its colors can be grouped.

[briandye]

[color:blue]Please see my responses in blue

First, since this is a "quote," can we assume that there is a quote line items table to hold these records?

[color:blue]I do have a table called QuoteItems linked to key field "QuoteID" I am however not sure how I should store the items in this table. Maybe a container field? Add and remove from container field if the user wants to add or remove the product.

As far as showing the 300 SKUs, there's more than one way to do it. But choosing one from a drop-down list, then hitting an "Add" button, would not be my first choice, if I was a user.

[color:blue]I would have to agree after thinking about this a little more.

Also, since you've got 3 or so different SKUs for 1 product, for colors, one would like to display just the one product, and let them pick the color.

[color:blue]Actually I have a little bit of flexibility here, the products all have the own SKU so same product different design or color (the product is artistic decor) gets a new SKU. For my sake of sanity and to group these I have created what I call a CollectionCode (a code just to group together like products)

Questions:

1. Does the user really need to see the SKUs to pick the product? Could they not tell by the name?

[color:blue]No they would not need the SKU, just the name of the collection.

2.a. Is there a Product table (without the color)?

[color:blue]Yes a product table and a record for each SKU, each SKU's record defines the variables for that product. The description for the product SKU is the same for every SKU in the same collection, the only difference is the picture of the product and the color of the product. I was going to grab the description from the CollectionCode table. Unless I should just do this in the product table and not even setup a CollectionCode table and just define the collection as a field in the product table.

2.b. Is the SKU of the final Product wColor based on the SKU of the Product?

3. If not, is there a way to positively group a "product" (all colors), with an indexed field?

What I'm thinking is a portal showing just the Products (not for any particular color), then, in the portal row, a radio button choice for the particular color desired (displayed horizontally; how many colors are we talking about here?).

[color:blue]Could be as many as 20 items in one collection, not defined solely on color but also design.

That would be a global field, its value list filtered for that product (if different products have different colors available).

After choosing the color desired, you can click a button in the portal to create a record in the quote line items with the SKU matching the Product AND gColor.

Something like that. But we don't even know whether you've got 1 Product table, or 2, what the SKUs look like, whether a product with its colors can be grouped.

[color:blue]

Let me give you an an example

This is for a Humming Bird Feeder called "The Top"

The following Item Numbers are for this product

12270 - Ruby

12271 - Amethyst

12272 - Aqua

Plus they all have their own UPC's driven by the product number (sku)

All of these items have the same description and catalog description, however price, cost and other elements could vary between these items. But I still need a way to group them together, currently the company is not grouping collections together, I figured this was something I had to do in order for them to show up on the same page in a PDF output of a presentation/quote generated by FM, hence CollectionCode.

Hope the info was sufficient.

Thanks again,

Brian

[Fenton]

It seems the obvious answer is to first choose the "collection", then pick from the items in it, which would appear in a portal, sorted to best present them. If it's only 20 items perhaps no further filtering is needed.

The "collection" choice would be from a global field (or perhaps a click on another portal to set a global).

The "items" portal would be filtered from this global collections choice to a "collection" field in the items table (Products?).

Whether or not "collections" is just a field in Products, or its own little table is pretty much optional. Either way you just need a value list with its items. Upon reflection, yes you should have a table for Collections, if that's where the Description is. But the desc. would have to be the same for everything. You need to think hard about just what is what here.

The data from the item would then be put into a new record in Quote Lines, as regular fields. I don't know why you'd think of "container" for this. You can print the final Quote to PDF, so you can make it pretty. Links to pictures should be valid, if you create the relationship for them.

It's sounds like you know more or less what to do. I'm not sure what you don't know how to do. You may want to upload a small sample of what you want to do. If your main files are big or complex, just create a simple one of the main characters involved. We don't need/want much.

Edited July 19, 2007 by Guest

[briandye]

Kind of getting away from value lists, but what would one think the best way to store this data in the quote line items table? I was thinking a text field with repetitions, a repetition for each collection that is to be a part of that quote...

Only thing I can't quite figure out is how to insert a field value from a drop down list displaying collections into the NEXT EMPTY repetition? I see you can specify the repetition to insert text into when inserting into a repeating field via a script, any one have an idea of a formula I could write to do this, I may have upwards of 100 repetitions in this field. Can anyone provide some insight?