Presumably you export the prices to an Archive Table. (Although you could just leave them in the Price Table and mark them as 'Archived'.)
You could create one field, a global date, and use that as the header for the column of records. You could then create a relationship from the Live Price Table to the Archive Table using that data. As you change the date you would see the other prices display from the archive table.
Good advice from Sorbsbuster, but to answer the specific question, you cannot add new fields to a table via a script.
Thank you for the replies. So if I am understanding correctly Sorbsbuster, I would have to create a new gobal date field every time I want to store the value in the 'history'?
No, for example, you could:
- have a listing of prices in your Price List file. This will show Product ID, Description, etc, and the last column might be 'Price'.
- duplicate the layout and replace the 'Price' field with the 'Price' field from the Archive Table.
- for this purpose that relationship to the Archive Table would be:
PriceList::ProductID = ArchiveTable::ProductID
gDateToView = ArchiveTable::ArchiveDate (or whatever is the date that specifies that particular batch of prices.)
You can then change the value in the gDateToView field and it will show you the price history for that date. You could of course have the two prices - live and archived - beside each other on the layout. Then you could calculate price change percentages, etc.
Feel free to add two dates, two relationships, and display two archived prices, etc ad nauseum.
Ah, that's where I was lost. I don't just want the last price and the current price, I want a whole archived section of every price as I go on. So a month from now, I would have price values for each item 12 times (3 updates a week for a month) with some way to correspond the date of the update with the old value. And obviously the list of historical values would keep growing.
So I would have to keep adding dates and relationships for every possible date I wanted to see?
No. If you want to see a bunch of say 6 at a time, you would create 6 relationships, and 6 global fields (with 5 probably calculated off the first one.) The strength of the global date in the relationship is that it lets you 're-use' the relationships. Change the dates and you see 6 different 'old' prices.
Another complementary way is to have a portal to the Archive Table, connected by ProductID only, and it will show you every price since time immemorial for that product.
If the script says go to layout (a table view) you can add the field there. I haven't 11 open but in 12 after adding the field I can set its type, etc. Oddly the new field doesn't show up in the table layout mode but it does in table view mode. It can be seen in the field editor and will appear on new layouts.
This is one reason to avoid giving Full Access priviliges to everyone!
Consider this method scripted with manual input...:)