Relationship issues -- calculation fields & storage

[jn08]

Hi,

 

I've got a central title database set up for a publishing house. They have promotional pricing that they need to schedule by date. I have a promotional pricing table set up with a start date, an end date, and the promotional price. A calculation field on this table determines whether the particular row on the table is 'active' i.e. should have pricing enabled.

 

I've got a relationship to this table with the main products table matching the ProductID and the Active field.

 

I would have thought this would work, but I'm having trouble on a number of levels and have tried a whole bunch of different combinations but it still doesn't seem to update day-to-day.

 

First-off I discovered that the relationship doesn't work unless indexing is on. So I turned it on and the calc field on the promotional table doesn't seem to 'update' the next day and therefore display the correct current promotional price. I've tried using auto-enter number fields, but they don't seem to update either.

 

Could anyone give me a best case scenario of how this relationship should work and what type of fields (with what options) should be used for the relationship? It's driving me insane!

[Rick Whitelaw]

For a more detailed reply you might consider posting more information like a relationship graph. That aside, set storage on any calc field that update s regularly to DoNot Store and update as needed.

[jn08]

Unfortunately when I set the regularly updating field to Do Not Store the relationship breaks and no information is displayed.

 

If it's set as "store only as needed" it appears to not update when the calculation changes.

 

Here are the two match fields:

 

ProductID (Number, Indexed, Auto-enter serial, Can't modify auto) --> ProductID (Text, Indexed)

Active Promo (Calculation, Indexed, = 1) --> Active (Calc, Indexed, from Promotional Pricing, = If( Get ( CurrentDate ) ≥ Promotional Pricing::Start Date and Get ( CurrentDate ) ≤ Promotional Pricing::End Date ; 1 ; 0)

 

Not sure if the relationship graph will help much but happy to post it up.

 

Thanks!

[jn08]

Could the issue be that the fields in the Product table that are accessing the promotional pricing are calc fields pointing to the related fields? Do they need to be fields inserted directly from the related table, or can they be used in calculations? The documentation on the "automatically create indexes as needed" option seems to suggest that new indexes are only drawn up when searching for the field -- not when accessing the fields in related calculations. Any ideas?

[webko]

IIRC, Get(CurrentDate) is static from when you open the file, unless you have set it to be Unstored - which means it can't be directly used in a relationship.

 

You could run a script once a day to update that value, which means you can store the field used for the match. The script could write the current date into the relevant field, and the field can just be a date field that is stored.

[jn08]

Hmm, that might be the issue. I have a script that runs on startup (which runs once a day as the database is scheduled to close and re-open overnight). The easiest way to integrate it into my existing solution would be to somehow 'refresh' that field so it updates, but I'm not sure how to do that. Is it possible?

[webko]

Make the field just a date field.

 

Make the script go to that field, then use Set Field: Get(CurrentDate)

[Fenton]

I am not so great at figuring out exactly what the relationship and fields are. it seems there are two options. Either change in field so that it can be for the current date, or use a an unstored calculation with Get (CurrentDate). I would likely do the later. While it is true that a relationship cannot target "to" an unstored field, you can use an unstored field to go "from" to one which has the indexed field you want to target. 

 

If you must use the same table, you can create another relationship occurrence of the table, using the unstored calculation to target the date field, using the same fields for the other options.

[jn08]

I think I've got this working, but I'm using a script to update a text field and having it loop through each record adding the current date whenever a new promotional price is updated, added or accessed as well as during the startup script every evening. This should cover all contingencies as far as I can tell. I tried to use a global field instead but it didn't seem to work with the auto-enter calculation.

 

Thanks for your help guys.