Comparing hours from one table to time slots in another table

[Nikos]

Hi,

i contact you as I have reopened a FileMaker db to follow electricity consumption. I have different prices acccording to hour of consumption. Then I created a DB

i have 2 tables:

- One table with 2 records corresponding to time slots when price is low and when it's not, with fields 'start time', 'end time' and price during this period, and price out of this range of time.

- One table where I put all the consumption data with hours and consumption

I linked the two tables with 2 links:

1. one between begin time >= Hour
2. one between end time <= Hour

It does not seem to word, as when I try to calculate if the hour is within one of the two timeslots it only recognise one of them...

the calculation is : IF ( Hour ≥ Tarifs::start time' And Hour ≤ Tarifs::end time;"Tarif HC";"Tarif HP")...

Where did I do wrong? Why FMP seems to recognise only one of the two records of the first table?

If someone can help? I wish I can send some printscreens to clearly explain the issues I face.

Thanks in advance.

Niko

[comment]

17 minutes ago, Nikos said:

I linked the two tables with 2 links:

1. one between begin time >= Hour
2. one between end time <= Hour

I am assuming it's the other way round? 

Anyway, the If() calculation makes very little sense to me, because the way your relationship is defined (assuming the above correction), the test will always evaluate as true unless there is no related record in the Tariffs table. If you want to know which tariff is applicable, get its name (or rate, or any other property directly from the related record in the Tariffs table.

I am also confused regarding the whole situation: are the three fields used in the relationship Time fields or Number fields? And how is it possible that "consumption" has only a single Hour field? I would expect it to have a StartTime and EndTime, and a process that divides the duration into the applicable tariff periods.

 

[Nikos]

Hi,

Indeed, sorry, it's the other way round... :).

And sorry for the 1st explanation. I attached some print screens to explain a bit more than words...

So, the below picture is the 1st table with the 2 records, corresponding to 2 time slots:

 

The second table is an extract from the provider with consumption every 30 mins. On this table I want to apply the correct price HP or HC depending on the time compared to the 1st table slots:

 

Currently I can almost correctly identify the 2nd time slot (except the midnight, the unique time considered as HC for the 1 time slot). And I cannot get the price for the other times... (I think if might be related to the links between the tables... I should maybe consider a constant value 1 in each table to link so that I can afterwards calculate regardless the time links)

 

The last two pictures are the links between the tables:

If that helps...

Thanks in advance!

Niko

Edited January 8 by Nikos
Edited as I changed the Table 2 header from 'Time' to 'Hour'

[comment]

I still don't understand  this:

• Your 2 Tarifs records cover only a part of the  24-hour day. What should happen to the Consumption records whose Hour does not match either one of the two?
• You say you "want to apply the correct price HP or HC depending on the time". I don't see how you you are supposed to do that. The time tells you which Tarif record is applicable (if any); it remains unclear what in the Consumption record determines which price to choose from the applicable Tarif record.

It's also confusing to see that both Tarifs records are identical except for the times. At least for testing, you should use different values, so you can verify you're getting the right record.

One thing is clear though: you have a problem with the tariff crossing midnight. There is no time in a 24-hour day that is both greater than 23:28 and smaller than 7:28 at the same time. So the way your relationship is defined now, this record will never be related.

There are several ways to solve this - the simplest one, IMHO, is to split the record in two: one from 23:28 to 24:00, the other from 0:00 to 7:28.

 

 

[Nikos]

Hi,

Ok i rearrange the DB according to your feedbacks, and I explain the purpose of this project.

In France, we have electricity providers. When we subscribe we can either take the 'base' program, with subscription fees and same price per watt across the day. Or we can subscribe to HC/HP program with specific subscription fees and 2 different prices per watt depending of the time of consumption during the day (HC (equivalent to low price) or HP(equivalent to high price)).

So, to evaluate which program is better for our consumption scheme, I want to create a DB to evaluate this.

Then, I created 3 tables:

• 1 with prices (subscription fees)
• 1 with the unit prices for HC or HP
• 1 with the extraction of consumption detail from the provider.

With this in maid, I tried to create the relationship between these 3 tables...

I'm not convinced by these links.

The blue table is the table where I have all the real consumption per 30 mins. This blue table is linked to tariffs table (in pink) with constant value (=1) so that I collect all value whatever the data in it.

Then, I created a link between the blue table with the grey one to identify the prices to apply. I only the HC range of price thinking that if it's not in that range the price to apply is the HP...

Do you think this is correct?

 Thanks for your help.

Regards

Niko

[comment]

I am afraid this is not getting any clearer.

If you have "different prices depending of the time of consumption" then you should have one table of Prices with fields for:

• FromTime
• ToTime
• Price

and it should have exactly one record to cover every time within a 24-hour period.

Then, if your consumption data comes in periods of 30-minutes each, you need to define a relationship between Consumption and Prices in such way that each  consumption record is related to the applicable prices in that period.

Note that at least in theory there can be more than one such period - unless your price periods also start and end at exact half-hours. The relationship you described in the beginning of the thread ignored this - possibly because the difference was only 2 minutes?