Date Calculation – Complex

[Melissa Smith]

I’m extremely new to Tap Forms, having just discovered it yesterday. I’ve created two basic forms and they’re working mostly as I need them to. However, there is something that has me I’m stumped – getting a “next due” date based on a field in a parent table and another field in a child table. I’ve searched the forum, but can’t quite find any posts that I think cover the question I have. I apologize if it has been answered somewhere that I’m missing.

I’m also new to using Mac’s, coming from a PC background. My database experience is with MS Access and MS SQL Server. I feel like I’m treading water a bit with the whole Apple experience, and I’ve been tasked with building a Routine Maintenance tracking system for my employer (using the Mac). I initially started using MySQL to build the backend, but couldn’t find anything comparable to MS Access to build the front end on our tight budget, so shelling out for something like FileMaker isn’t going to happen. (Tried LibreOffice, but that experience made me want to punch the computer!)

Here’s the nitty-gritty:

I have two forms to track Routine Maintenance built in TapForms. The parent form is called “Equipment”, and the child form is called “Maintenance”, in a 1:M relationship.

In the Equipment (parent) form, I have a numeric (no style) field called “Frequency” to enter the number of Days each piece of equipment should have a routine maintenance visit performed. Different equipment will have different frequencies (the frequency is ultimately set by our customers), so that number will not be static. That Frequency field also has a pick list with the following choices: 91, 121, 182, 365.

For example, if the equipment should be serviced quarterly, the Frequency would be 91. If it’s being service bi-annually, then it would be 182, etc…

In the Maintenance (child) form, I record when each routine maintenance was completed for that piece of equipment. Eventually that child table may have hundreds of records for each piece of Equipment.

What I want to calculate is when each Equipment’s Next Due date will be from the last (latest) completed Maintenance record based on it’s Frequency. This is something that I’m familiar with writing in SQL. I’d create a view that links the Equipment table to the Maintenance table, group it by Equipment ID, find the MAX “date completed” for each Equipment record, and then create an expression that calculates the next due date (this is the MSSQL syntax for the calc field):

DATEADD(day,dbo.maintenance.Date_Completed,dbo.equipment.Frequency) AS Next Due

So if the MAX date was 1/1/2014, with a frequency of 91 days, the “Next Due” date should be 4/2/2014. That Next Due field would then be tacked onto my parent form, dynamically updated when a new maintenance record was created. That would allow us to see at any given time when to schedule the next service. (I’d also like to be able to print out a report to hand off to my boss that shows all the equipment that’s coming up as “due” or any that are “past due”.)

My idea for a workaround was to include a “Last Date” field in the Equipment table that I would manually change when I was updating a new Maintenance record (so I’d be entering that date in two places – the parent and the child forms). I also tried setting up a calculated field (in the Equipment table) to determine what the next due date would be: Last Date + Frequency = Next Due. But I can’t figure out how to write the formula to get it to return a date value, if that’s possible. I read another post that said a conversion was necessary, but I’ve only managed to get a 0 returned.

Am I approaching this wrong, or is this not something that is supported at this time?

I appreciate any feedback or suggestions.

[Brendan]

Hi Melissa,

Tap Forms at the moment will only return the number of seconds as the result of a calculation that includes a Date field type. It does not yet give you an absolute date value after performing a calculation. You can do things like find out how many days, months, years, hours, etc are between different dates and you can do whatever other mathematical operations you want to a date value, but it won’t give you an absolute date as a result. The reason for this is that a date is simply represented by the number of seconds from January 1, 1970. This is known as the Unix epoch.

So what you’re trying to do at the moment is not possible with Tap Forms.

That being said, it’s just a matter of time before I allow an absolute date value to be returned from the result of a calculation. I just have been working on other features at the moment.

Thanks!

Brendan

[Z Nielsen]

I’d like to offer my vote for the importance of this feature. I’ve been using this app for over a year and never have really fully integrated the application into my process because I can’t get a good date calculation. I have a requirement to see the date a few weeks out and at different intervals such as 52 days etc. The application is perfect for field recording of data, but it lacks that critical calculation to be extremely useful.

[Juan Perez]

When I subtract one date from another I get a very large number which, from reading Brendan’s post, I guess its the number of seconds… How can I Tap Forms to display the result in days? Thanks

[Leo]

Devide the result by (24 hours * 60 minutes * 60 seconds) = 86400 gives the number of days.

[dicki]

Looking for this option as wel.
Option next due date.
Need it for windowcleaning scheduler.

Any idea when date calculation for due date will be availeble?

Tapforms looks great. Thanks for your hard work.

[Brendan]

Hi Dicki,

Well you can do some dater calculations now and have Tap Forms return an absolute date result. To do that, add a Calculation field to your form, then set the Result Type to Date.

Now make a formula such as the following to add 10 days to the Invoice Date:

[Invoice Date] + 10 * 24 * 60 * 60

The 10 * 24 * 60 * 60 bit converts 10 days into seconds. All date calculations in Tap Forms operate on seconds. So you just need to do a bit of math to get the kind of units that you need for your result. In some cases you need to divide if you want to turn seconds into minutes, hours, days, or years, etc.

Thanks!

Brendan

[dicki]

Hi Brendan,

Sounds great.

For windowcleaning scheduling in can use this.
To get the next_Due_date

[Work_done_date] + 28 *24*60*60 Means once in the 4 weeks

But now i want to create an “Create_Job” form

There will be an dropdown field with :Day, week, month, year
and an number-field Frequentie where i can put an number 4 in.

How can i combine them to get the number 28?

Otherwise i have to count each time the number of days and that is what i don’t want.

Hope you understand this question.

Thanks anyway,
greetings

[Brendan]

Hi Dicki,

Hmm… I’m not quite following along. But perhaps instead of 28 hardcoded into your formula you can put in [Frequency] * [Unit] in its place in your formula. The Unit dropdown could contain the values for the number of days. E.g. 1, 7, 30, 365.25.

Thanks,

Brendan

[Author]

Posts