Posting Pricing but Pricing not set Daily

[lbgrn82]

Hello,
I'm attempting to assign a price to a transaction based on the transaction date. There is a table with price changes and dates to post from BUT the prices don't change daily. So a post operation that uses "Date" as the linking key will only post prices to transactions that occurred on a price change day and the others remain blank. I'm needing the blank ones to have a price of the previous price change. Below is an example of the "Price Change Table" where changes are stored and what the "Transaction Table" should look like after the posting/update. I'm hoping there is a simple solution and I apologize if it's too simple to be asking!

Price Change Table

7/1	1.00
7/3	2.00
7/5	3.00
7/7	4.00

Transaction Table

7/1	1.00
7/2	1.00
7/3	2.00
7/4	2.00
7/5	3.00
7/6	3.00

[CraigSchumacker]

I think you need a part number (or item number) field to link on; not the date. If it's applicable to all lines, just leave the field always blank.

Then, assuming you are using 8 character date fields, you could use replace on the price if the date in the transaction table is >= to the date in the price change table.

So, for 7/6 in the above example, it would put in $1, then replace with $2, then replace with $3 never getting to the $4.

But the above is sloppy and slow.

You might consider instead adding a finish date field to the price change table allowing the transaction date to fall within a range.

[Mike Wilson]

My guess is that using a post operation is not the method to accomplish what you are attempting unless you update the price change table daily.

[Tom Cone Jr]

I think Mike's point deserves elaboration. "Operations" in AA are intended to be used when appending, deleting, updating or changing multiple records in one step. There are other techniques available to change a field in a specific transaction record. If you mean to post prices to multiple records in a single operation I think you'll find the post operation can't be programmed to do what you wish, where your "source" table has gaps. I see two alternatives:

1) change your source table so that there's a date and price for every day in the year; or

2) write a custom xbasic script that steps through the transactions and for each one: searches for a matching date in the source table; if found, change the price in the current transaction record - if not found, search again, for the previous date ... continue searching backwards til a match is found; change the price in the current transaction record.

[Ted Giles]

Sorry to be blunt, but this is not a good design.
In engineering,there are data called Effectivity Dates. Start and end of a period when a value or part is covered.

In the request, the users don't have to enter a date for the item? That's not good. As Craig says, you need a common field - say an ID to relate the price to.
Then, instead of an iteration as Tom suggested, if date doesn't exist, use a filter to get the LAST record where the ID's match as a Default Vaule.

In the real world, I suspect there are date ranges when item prices vary up or down, so the base design needs a good look at, may I suggest?

[lbgrn82]

Thank you all for your input! I'm still trying to get my head around it all...

In the actual tables there are Product Codes that can be linked as a common field. I'm able to have a start and end date in the "Price Change Table" but am unsure of what operation I should use to get the pricing over to the transaction table. Below are examples of what I have, and what I'm trying to accomplish with the added Product Code and End Date. I'm not sure if this makes a difference in they type of operation but the transaction table will end up being quite large (100-1000 transactions a day x 18-24 months).

Pricing Table
Product ID	Start Date	End Date	Price
001	1/1	1/15	$1.25
001	1/16	1/20	$1.50
001	1/21	1/30	$1.75
002	1/1	1/15	$3.00
002	1/16	1/20	$3.25
002	1/21	1/30	$3.50

Transaction Table
Product ID	Date	Price
001	1/2	$1.25
001	1/18	$1.50
001	1/25	$1.75
002	1/3	$3.00
002	1/19	$3.25
002	1/21	$3.50

[lbgrn82]

an additional note: I have a logical field in the transaction table to note if the transactions have been billed. Once they are billed, the prices shouldn't be able to be altered ( i.e. changes to pricing table on accident?)

[Ted Giles]

Is this an Invoicing Application?

If so, there is more information needed in order to process the transactions.
I am presuming that;
Customer buys item
Price of item is dependent on a date range
Transaction Table holds details of customer, price, quantity and billing/paid flag
Invoice is created from the Transaction Table.

Close or not close?

[Tom Cone Jr]

It's still not clear whether you (lbgrn82) are trying to run an operation against all the transaction records, or whether you are trying to set the correct price for an item in a single transaction record during data entry. These are very different things. Please clarify.

Feel free to send me a private message through this message board if you wish.

[lbgrn82]

Hi all, it is an invoice application. The individual transactions are imported in batches via CSV but without pricing. Invoices are created every 2 weeks to include all of the individual transactions a customer makes during that time period. It's fuel products so prices change frequently but not necessarily daily. There are 10s of products, 100s of customers and 1000s of transactions for each billing period (invoice). It sounds like it might be easiest for me to create an operation that copies and pastes the previous day's prices so there will be a price for each day of year for each product and then a post operation that applies prices for the unbilled transactions?

[Ted Giles]

I think you will end up with unhappy customers and a mess of trouble.
The "Striking Date $ Value" is essential.
If someone places an order for, say 100 Litres of Heating Oil, they will have looked around and established the best price available. That's the price they expect to pay.
If as your app suggests, they don't care, and trust you to bill whatever you want, that seems like a recipe for disaster.

When you get the CSV, is there a reason that no pricing information is included? I would have thought that the data entry would have captured that.
Or have I misunderstood?

You could indeed copy the previous day's prices so that each day has a price value, so that is one approach Another is to capture the daily price somehow and automate the update process. Depends on how, how and where you get the pricing info from.

[Mike Wilson]

Ok, I'll say it. I believe Ted was tending towards this. Your design is hurting yourself. Your Pricing table engenders a less than desirable filtering needed to get to the price for the transaction. I would work off the principle of one day one price and rid myself of a date range construct. There must be a daily/weekly price update, so I would transform this to one day one price model. If the table gets large, archive...

[madtowng]

This is one of those time when mysql works so much better than dbf tables (I'm making worst case assumptions here.
If you are using dbf tables, there are ways to use sql without going through the nightmare of moving everything to sql,
provided you can dump your data into csv/txt files.

 select t.product_id, t.date `Date`,
(select q.price from pricingTable q where q.product_id = t.product_id and
t.date between q.startDate and coalesce(q.endDate,curdate()) ) `Price`
from TransactionTable ...

[Ted Giles]

You could use the "between " test for dates in DBF's, Greg.
The original issue was not having a value against a date, so the last available one was needed - whenever that was.
I have looked at finding the last record with a value in it, but that doesn't get over the issue of missing data.

Given the original scenario, capturing the last value and using that WHEN there no date and value data avialable would work.

[madtowng]

Ted,
As far as I know, we do not have a coalesce() type function when working with dbf tables.
I believe coalesce() makes the code work as desired.