Source language: Translate to:

Updating config edits to reflect in Order Details

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Updating config edits to reflect in Order Details

Postby schmutly » Sun Nov 30, 2014 2:07 am

Hello. Ive heavily edited the DBPro Invoice demo and added themimg, weight , currency convertor etc..all work great.
But i need to know, is it possible how to UPDATE the Order details to reflect changes done to PRICING from the CONFIG
page?
If i change the price of something from 20 to 25 can i have it update ALL the "currently selected" items in the Order
Details page to UPDATE those prices? At the moment i simply have to use the search i created, delete them and
re add them but when there's a lot of items in a order can get a bit laborious.
Can this be done?


Robbie

Image

Image
Last edited by schmutly on Tue Dec 02, 2014 4:44 am, edited 3 times in total.
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Re: Updating config edits to reflect in Order Details

Postby Gaev » Sun Nov 30, 2014 7:58 am

schmutly:
But i need to know, is it possible how to UPDATE the Order details to reflect changes done to PRICING from the CONFIG
page?

Yes, but first you need to clarify your exact requirements.

If i change the price of something from 20 to 25 can i have it update ALL the "currently selected" items in the Order
Details page to UPDATE those prices?

I am unclear about what you mean by "selected items" ... you can only "select" one record (row) in a "Grid' at any one time ... do you mean "all the records (rows) present in the "Grid" ... including some that may be viewable in the designated Rectangle by using the scrollbar ?

If so, you can loop through this set of records by using ...

1) [ID.Table.$RecCount] to obtain the count of records in the set

2) dbpFirst to navigate to the first record in the set

3) a Loop/EndLoop code block using ...

a) [ID.Table.$RecCount] for the "stop value" parameter of the Loop command

b) [ID.Table.FieldName] to reference (both read and write) the value contained in the desired field of the current record in the set

c) dbpNext to advance to the next record in the set


Note: If only records with a certain "Product ID/Description" need to be updated, you can place the If/Else/EndIf code block inside the Loop/EndLoop code block


For more details, take a look at the section called Creating Database Enabled Publications in the Help file.

If you need help with the code, please provide your details of names used for the ID, Table, FieldName and the variable containing the replacement value.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Updating config edits to reflect in Order Details

Postby schmutly » Sun Nov 30, 2014 9:46 am

Hi Gaev,
i read what i wrote, maybe it wasn't clear sorry.
Thinking about all i want to do is.
As in any image above the Sandisk Ultra SD highlighted is 29.50 that's been added to order 10001.
I want to change that price in the config page to 30. If i go to config and change it when i return to
this orders page, the price is still as 29.50, understandably..but i simply want that ORDER to reflect
the change when i review it again..so its updated with the new 30 price.
At present, i have to delete that item and THEN add it again, whereby it will of course show the NEW updated 30. By highlighted, as you said only one item in grid at a time, correct, that's fine.. But sometimes the Shops
i get the stuff from change slightly but only a few now and again..but id like to change it in the CONFIG page
and want that to update (reflect the change) in this Order Details Page.
Is that a bit clearer? I don't really need to loop through the lot just to change one item, its more the NEED to
have it change to the NEW price on that Order 10001. Hope that's a bit clearer
Thanks,
Rob
ps: if i can SEE how to do that for price when the description needs changing then i'd understand from this answer and can do
it with that field to... Is there an SQL statement that once i CHANGE the PRICE field in the CONFIG that it will update that PRICE in the
[Order.Details.Price] from the newly updated price in the parts (config) page: [Order.Parts.Price]
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Re: Updating config edits to reflect in Order Details

Postby Gaev » Sun Nov 30, 2014 12:56 pm

schmutly:

Edited after initial post ...

As in any image above the Sandisk Ultra SD highlighted is 29.50 that's been added to order 10001.

I want to change that price in the config page to 30.

If i go to config and change it when i return to this orders page, the price is still as 29.50, understandably..but i simply want that ORDER to reflect the change when i review it again..so its updated with the new 30 price.

In the Page Exit section of the Config Page, you can do ...
Code: Select all
SetVar "[Order.Details.Price]" "[Order.Parts.Price]"

... although it might be better to trigger this via a Button.

Check this in the Debug Window ... if the Grid does not refresh, you may want to place a dbpHideGrid followed by a dbpShowgrid in the page's PageEnter section.

Is there an SQL statement that once i CHANGE the PRICE field in the CONFIG that it will update that PRICE in the
[Order.Details.Price] from the newly updated price in the parts (config) page: [Order.Parts.Price]


Yes ...
Code: Select all
UPDATE Details
SET Price=[Order.Parts.Price]
WHERE Order_Number=[Order.Details.Order_Number]


Hope that helps.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Updating config edits to reflect in Order Details

Postby schmutly » Sun Nov 30, 2014 4:56 pm

Thanks Gaev,
Ill give those suggestions a try,
Rob.
update:
Just a few issues,
Using: dbpExecSQL "Orders" "UPDATE OrderDetails SET UnitPrice = [Orders.Parts.UnitPrice] WHERE OrderNum = [Orders.Invoices.OrderNum]" "" this
works but:
1. it updates all items in Order.OrderDetails view to same price.
2. It doesn't update the total price in that details grid. I will look at the updatetotals and partUpdate subroutines to
get an idea. I think your right as i might need to iterate through the parts to SEE if any are changed.
Qn: So if i change in the config page 3 items prices then click 'done' button would i need to
loop through comparing each part in config to orderdetails?
I.E: Orders.Parts.UnitPrice compare to Orders.OrderDetails.UnitPrice if different , update
and THEN "UpdateTotals" in Orders.Invoices.OrderNum.

I will spend a bit of time having a look at this and thanks for any suggestions.
Rob.
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Re: Updating config edits to reflect in Order Details

Postby Gaev » Sun Nov 30, 2014 7:20 pm

schmutly:

1. it updates all items in Order.OrderDetails view to same price.

Change the WHERE condition to look for matching PartNum values ...
Code: Select all
dbpExecSQL "Orders" "UPDATE OrderDetails SET UnitPrice = [Orders.Parts.UnitPrice] WHERE PartNum = [Orders.Parts.PartNum]" ""


2. It doesn't update the total price in that details grid. I will look at the updatetotals and partUpdate subroutines to
get an idea.

1) I think you will need to also update the TotalPrice in the dbpExecSQL command ... either using the same dbpExecSQL command or with 2 of them ...
Code: Select all
dbpExecSQL "Orders" "UPDATE OrderDetails SET UnitPrice = [Orders.Parts.UnitPrice], TotalPrice = [Orders.Parts.UnitPrice]*Qty WHERE PartNum = [Orders.Parts.PartNum]" ""


or

dbpExecSQL "Orders" "UPDATE OrderDetails SET UnitPrice = [Orders.Parts.UnitPrice] WHERE PartNum = [Orders.Parts.PartNum]" ""
dbpExecSQL "Orders" "UPDATE OrderDetails SET TotalPrice = [Orders.Parts.UnitPrice]*Qty WHERE PartNum = [Orders.Parts.PartNum]" ""


2) Yes, I think you will need to GoSub "UpdateTotals" after the dbpExecSQL command(s)


Qn: So if i change in the config page 3 items prices then click 'done' button would i need to
loop through comparing each part in config to orderdetails?
I.E: Orders.Parts.UnitPrice compare to Orders.OrderDetails.UnitPrice if different , update
and THEN "UpdateTotals" in Orders.Invoices.OrderNum.

Too complicated ... instead add another Button (labelled "Update Price in OrderDetails") ... have the click of this Button trigger the dbpExecSQL and the GoSub commands ... so you would update one price record and click on this Button ... that way, [Orders.Parts.PartNum] and [Orders.Parts.UnitPrice] would be fetched from the currently selected Parts record.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Updating config edits to reflect in Order Details

Postby schmutly » Sun Nov 30, 2014 8:00 pm

Thanks Gaev.. :D
Thats fantastic.
I only needed to put your:
dbpExecSQL "Orders" "UPDATE OrderDetails SET UnitPrice = [Orders.Parts.UnitPrice], TotalPrice = [Orders.Parts.UnitPrice]*Qty WHERE PartNum = '[Orders.Parts.PartNum]'" ""
in the DONE BUTTON on the Config page and i edited all 10 prices to $1 and all those orders prices X Qty updated correctly on that order... :D
I just had to add the single quotes around the Orders.Parts.PartNum as it was alphanum. as you reminded me once, so that's great.
Again, thanks very much..
Rob
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest