Quoting System

[bavltd]

Hi All,

I would like to create a quoting system that is similar to the Quotewerks system available for PC which we currently use - www.quotewerks.com. I have created a database with two tables. One contains details about the customer etc, and the other contains the products related to that quote. They are linked via a QuoteID field.

I have created a layout with a portal and all is great however I would like to be able to do the following in the portal:

1. Insert a new "row" in the middle of the existing portal list (like I would in a spreadsheet)

2. Copy and paste rows (copy a row, insert a blank row and then paste the copied row into it)

3. Be able to insert a row that would either show a running subtotal (addition of all the rows from the start to this point) or a subtotal (additon of the rows above this point to the last subtotal line).

4. Be able to insert a "memo" type line which only contains text & no costs.

5. Be able to select which lines get output to the final printed copy. There are some clients we have that we don't itemise costs for. The section in the entry system would start with the memo line and so the print out would just show the memo line and the subtotal for that section.

Having looked at the tables in quotewerks (based on access) there is a linetype field which tell the system if the line is an item, subtotal or running total. There is also a line order field which gets updated when the quote is saved to reflect the edits that have been done.

Any suggestions how to approach this would be greatfully received.

Thanks

Jonathan

[Tintagel]

Clearly you need to create a linetype and order field in your quotes table, then do a bit of nifty scripting to control the portal.

You'll be able to manage something like 1, 2 and 5 with scripts. 3 could be trickier - you'd be better using a preview with sub-summaries in the quotes table, rather than using a portal.t just blows the data model totally.

4 just seems to me like a case of fuzzy thinking. Why ever would you want to have notes *in* your quotes table?! Attached to it I can understand, but putting them *in* the portal?!

:badidea:

[bavltd]

The "memo" entry is really just another portal record with no amounts in it, just the product description field.

I've managed to do the insert row with a script and also the running subtotal using a summary, but am stuck on the copy/paste functionality.

Everywhere I have read says that copy/paste from the menu doesn't work, so I though about creating another table to act as a clipboard and use a lookup to insert data from the original table.

My problems now are:

How do I get the information back into the original portal with the new line numbers - I can't use a lookup in the same way as the fields already have lookups in. Do I have to do lots of setfields?

How can I select multiple rows from the portal and copy them as I would in excel? I guess I need to get some way of calculating the end selected portal and creating a loop starting with the first selected portal and running for x times. How do I show multiple selected portal rows to the user?

[Søren Dyhr]

No-one said that the clipboard doesn't work, only that it's a bad habit.

But you have a flaw in you reasoning! Realtional databases are as such attempts to prevent suncronization of data, which means each unique data should be stored in just one location, and then refernced instead.

Turning an order to invoice, is simply done with provision of yet another keyfield to the jointable record, to make it's data show up elsewhere say the invoice for instance ...so the entire copy business isn't what's required.

What you do in your quote line is to attach a button that gathers this particular lines unique ID in a returndelimited manner, say in a $$variable, which then is used when the invoice is made.

But I have actually considered making you a template with ideas to ways to solve your issues, unfortunately are customers disturbing be occationally ...but if others doesn't have anything up their sleves will I procede make you one!

--sd

[bavltd]

hi sd,

I have attached what I have done so far. I am really trying to fix the following two problems.

1. Copy multiple records from a portal into the "clipboard table" so that they can be pasted (re-imported) back

2. Add a subtotal line to the portal which will total all the entries between the current line and a previous subtotal.

I think i have everything else working although I would welcome any comments on how I shouldn't have done it! The logic works for my brain but may not be the best.

I have used zippscript to update certain fields with calculations. I haven't included the contacts etc referenced by the file either, but it should be ok without.