fmp17 Need help regarding Meter Reading Schedule

[Tondats]

Hello guys, good afternoon.

I really need your help guys. I couldn't solve this problem. Currently I'm making a solution about Meter Reading Schedule.

My problem is, I couldn't skip these dates November 1, December 25, and January 1. What I want is to skip those dates. My Reading Date column works great. I don't have problem of it. The problem is my Due Date and Disconnection Date column.

Here's the sample calculation of my Due Date:

Let ( 
[
$itself = ReadingDate14 + 10;
$investigation = Case ( 

DayName ( $itself ) = "Monday"; $itself;
DayName ( $itself ) = "Tuesday"; $itself;
DayName ( $itself ) = "Wednesday"; $itself;
DayName ( $itself ) = "Thursday"; $itself;
DayName ( $itself ) = "Friday"; $itself;
DayName ( $itself ) = "Saturday"; $itself;
DayName ( $itself ) = "Sunday"; $itself + 1

 );

$compare = DueDate13;
$halloween = Date ( 11 ; 1 ; Year ( ReadingMonth ) );
$christmas = Date ( 12 ; 25 ; Year ( ReadingMonth ) );
$newyear = Date ( 1 ; 1 ; Year ( ReadingMonth )+1 );

];

Case ( 
$investigation ≤ $compare; $investigation + 1;
$investigation = $haloween; $investigation + 1;
$investigation = $christmas; $investigation + 1;
$investigation = $newyear; $investigation + 1;
$investigation ≠ $compare; $investigation;
$investigation ≠ $haloween; $investigation;
$investigation ≠ $christmas; $investigation;
$investigation ≠ $newyear; $investigation;
 )

 )

Hope someone can help me here co'z I'm already frustrated with this problem. Thanks!

 

Edited June 23, 2018 by Tondats

[Steve Martino]

How are the fields being populated in the first place?  Is the reading date entered manually, then due date and disconnect date calculated?

It also looks like you can't have a due date on a Sunday, so you switch it to Monday.

And you don't want a due date, or a disconnection to fall on a blackout date.

Are those assumptions correct?

If that's your exact calculation there is one technical problem with it.  After you declare your last variable, there should not be a ; (semi colon like all the other variables)  just a ]; (bracket then semi colon).  

Other than that, you don't really say how the script fails.  Did you try breaking it down to smaller chunks and watching it with the script debugger and data viewer?

 

As a side note, it can be confusing to use a $ when setting Let variables.

Edited June 23, 2018 by Steve Martino

[Steve Martino]

---edited post to remove answer and add a file---

 

See if this works.

MeterReadings.fmp12

Edited June 23, 2018 by Steve Martino

[Tondats]

Thanks for effort Steve. I really appreciate it. I will download your work now and gonna try it. I'll get back to you about this. Thank you Steve!

[Tondats]

Hello Steve, good afternoon.

Your calculations works great. Two thumbs for you man. However, I need your help again guys. 

I created an Event table where the user input those specific holidays which are declared by our President. Let say, if our President here in our country declare June 25, 2018 is special holiday to honor something. What I want to happen is, the date will be skipped and it will be replaced by the next date in the field. Let say June 25, 2018 is declared as holiday, the user input that date in the Event table and then on the Reading Schedule Layout the field will set to June 26, 2018. 

Please download my partial Reading Schedule below. Please help me with this problem. Thanks!

Username: Admin

Password: [just leave it blank]

Reading_Schedule.fmp12

Edited June 25, 2018 by Tondats

[Steve Martino]

You file and my file differ completely.  I don't recommend doing it your way with multiple fields.

The layout should be in list view and one record should only consist one field for each column.  If you want to see/total records by month (time period) you should use a sub-summary report layout and finds.

[Tondats]

Hi Steve, good morning from the Philippines here.

The reason why I use multiple fields because my client wants to set only one field then the rest will be automatically computed/calculated. They don't want to input records one by one for the scheduling, what they want is set only one field then the rest will automatically be calculated based on what they have given "added days". Furthermore, there are times that my client needs to set the reading date to Saturday because there are months have only 28/29 days and lots of holidays on that month as well. So they have no choice but to set it to Saturday of other Zones read date.

Question about your Recommendation:

If I follow your recommendation Steve, my question is, does it automatically calculate the rest of the records? What I mean is, when I set the Reading field in the Reading column, does it automatically calculate other Reading Date records, Due Date records, and Disconnection Date records as well? If yes, can you teach how to do it.

Let's go back to my main problem:

My main problem right now Steve is this. What I want to happen is, the date will be skipped and it will be replaced by the next date in the field. Let say June 25, 2018 is declared as holiday by our President, the user input that date in the Event table and then on the Reading Schedule Layout the field will automatically set to June 26, 2018. But the problem is, I don't know where to start and how to do it. Currently I'm still thinking how to solve this problem. Hope you can help me with this. 

I'm very thankful that you help Steve.  Two thumbs for you man.

Edited June 25, 2018 by Tondats

[Steve Martino]

23 hours ago, Tondats said:

Hi Steve, good morning from the Philippines here.

The reason why I use multiple fields because my client wants to set only one field then the rest will be automatically computed/calculated. They don't want to input records one by one for the scheduling, what they want is set only one field then the rest will automatically be calculated based on what they have given "added days". Furthermore, there are times that my client needs to set the reading date to Saturday because there are months have only 28/29 days and lots of holidays on that month as well. So they have no choice but to set it to Saturday of other Zones read date.

I assume then 1 record equals 1 customer.  But for a given month a certain number of readings must be taken?  If so, what is the criteria for how many readings need to be taken in a given month?  I'm under the impression that a field is missing, a reading amount field that will be populated on the reading date.

Quote

Question about your Recommendation:

If I follow your recommendation Steve, my question is, does it automatically calculate the rest of the records? What I mean is, when I set the Reading field in the Reading column, does it automatically calculate other Reading Date records, Due Date records, and Disconnection Date records as well? If yes, can you teach how to do it.

I don't understand what you mean by "...when I set the Reading field in the Reading column..." unless it refers back to my first comment.

It may help to step back and explain exactly what the database is suppose to do, and what result/display/report do you expect to produce.

If your original screenshot is from Dec 2018, then it appears the selected reading dates are from Monday to Friday.  Do you only take readings from M-F unless one of those days is a blackout date?

 

Edited June 26, 2018 by Steve Martino

[Tondats]

Hi Steve, good evening!

Quote

I assume then 1 record equals 1 customer.  But for a given month a certain number of readings must be taken?

Currently, I'm making a solution for a government agency here in our country and one of this is the Reading Schedule. And up to now there are still using MS Excel for the Reading Schedule and they input it manually. Although they are using MS Excel like what I've said they are still input those Reading Dates, Due Dates, and Disconnection Dates MANUALLY. There are 36 zones and every zone there are about 200 to 600 concessionaires approximately. So, there are 9,500 concessionaires approximately.

 

Quote

I don't understand what you mean by "...when I set the Reading field in the Reading column..." unless it refers back to my first comment.

As you can see the video below, there are four columns. Column for Zone Numbers, column for Reading Dates, column for Due Dates, and column for Disconnection Dates. What their office wanted to happen is, they want to set only one date for the Reading Date column then the rest will automatically be calculated (See the video below). The first date that I set on the top is the Billing Month consumption. The second date that I set, that's the first field for Reading Date. So, when I set the date of the first field of the Reading Date, the rest of fields will automatically calculated. That's what they wanted to happen Steve.

 

Quote

Do you only take readings from M-F unless one of those days is a blackout date?

There are times, they have to put a reading schedule on Saturday due to certain reasons, such as Typhoon, Heavy Rain and Holidays in our country. If there is a typhoon and heavy rain, the schedule will be changed after the typhoon is gone. If the Reading schedule is within June only then within June only. That is why there are times they need to schedule on Saturday within month of June only.

Thanks for helping me Steve.

Edited June 28, 2018 by Tondats

[Tondats]

Anyone?