Hi all,
I have 3 tables, PriceBook, PriceGroup and PriceDiscount tables.
In the PriceDiscount table, I stored the discount rate with date and Product_id. I made One (priceGroup) to many (priceBook) set. I want to read the "Discount_rate" field with the date range from my set table and store here. I used the Query.filter method but my script kept reading only first record.
PriceDiscount table has
"SeriesVendor, StartDate, EndDate, Discount_rate"
LT253V003 1/1/07 12/31/07 20%
LT253V003 1/1/08 12/31/08 25%
LT253V003 1/1/09 12/31/09 30%
PriceGroup table has
"PriceGroup_id, Vendor_id, SeriesVendor"
LT253a V003 LT253V003
LT253a V001 LT253V001
PriceBook table has
"PriceBook_id, priceGroup_id, BookPrice, StartDate, NetPrice
abcdef LT253A 5.00 1/1/07 4.00 *apply(Discount_rate)
Here, my filter scripts and I wrote in the Onsave FieldRule
Why did I get only one first record which was wrong date?
Is my "Query.filter = ... between( date range)" wrong?
I have 3 tables, PriceBook, PriceGroup and PriceDiscount tables.
In the PriceDiscount table, I stored the discount rate with date and Product_id. I made One (priceGroup) to many (priceBook) set. I want to read the "Discount_rate" field with the date range from my set table and store here. I used the Query.filter method but my script kept reading only first record.
PriceDiscount table has
"SeriesVendor, StartDate, EndDate, Discount_rate"
LT253V003 1/1/07 12/31/07 20%
LT253V003 1/1/08 12/31/08 25%
LT253V003 1/1/09 12/31/09 30%
PriceGroup table has
"PriceGroup_id, Vendor_id, SeriesVendor"
LT253a V003 LT253V003
LT253a V001 LT253V001
PriceBook table has
"PriceBook_id, priceGroup_id, BookPrice, StartDate, NetPrice
abcdef LT253A 5.00 1/1/07 4.00 *apply(Discount_rate)
Here, my filter scripts and I wrote in the Onsave FieldRule
Code:
dim tPrcPbk as p dim Shared vPbkVndrId as c dim shared vPbkSdate as d tPrcPbk= table.open("pricebks") vPbkVndrId= tPrcPbk.Prdctvndr_id vPbkSdate = tPrcPbk.Pbk_start_date dim tPrdVndr as p tPrdVndr = table.open("prdctvndrs") query.filter = "Prdctvndr_id = Var->vPriceVndrId" query.order = "" query.flags = "" tPrdVndr.fetch_first() DIM Shared vSeriesvndrId AS C vSeriesvndrId = tPrdVndr.eval("Seriesvndr_Id") dim tPrcDis as p dim shared vDisSdate as d dim shared vDisEdate as d tPrcDis = table.open("pricediscount") query.filter = "(Seriesvndr_id = Var->vGet_Seriesvndr_Id) .and. between(vPbkSdate,{"+dtoc(tPrcDis.Startdate)+"},{"+dtoc(tPrcDis.Enddate)+"})" query.order = "" query.flags = "" tPrcDis.fetch_first() DIM Shared vDiscRate AS N vDiscRate = tPrcDis.eval("Discount_Rate") 'Write to table tPrcPbk.change_begin() tPrcpbk.Discount_rate = vDiscRate tPrcPbk.change_end(.t.) tPrdVndr.close() tPrcDis.close() tPrcPbk.close()
Is my "Query.filter = ... between( date range)" wrong?
Comment