App that works "forwards" and "backwards" (This ti...

Skip2MiLu · 02-10-2020 01:58 AM

Hi,

I have the following columns (column types):

Meeting Start Date (Date)
Meeting Start Time (Time)
Meeting Duration (Time) ?
Meeting End Time (Time)
Meeting End Date (Date)

You will see that I have purposefully made Meeting Duration a time column type and not a duration column type.
I have done so as I would like to be able use the “time selector window” or any other similar type setup to be to input duration vs needing the user to manually input the hours and or minutes and possible make a mistake.
I however think that maybe the time column type is not the ideal choice for my use case for the following reasons:

I will be limited to hours / minutes withing a day. Not sure how am I going to handle meetings that span over more than one day
I think the expression I want to use in my below use case scenarios will not work if Meeting Duration is a time column type.

Below are two use case scenarios when I am entering data in my form:

Scenario 1 Meeting Start and End Date are within the same day:

I select the date and time in Meeting Start Date & Start Time. Then for example I select 1 hour and 30 min (i.e 01:30) in Meeting Duration column and then I have an initial value expression that calculate the Meeting End Date & End Time columns factoring in the values that where added to the Meeting Start Date & Start Time columns.

Scenario : Meeting Start and End Date span over more than one day:

In this scenario I would input Meeting Start Date and Time as well as Meeting End Date and Time and the Duration initial value would calculate the duration, but to be honest I think if the initial value used return “All Day”…it would be better option.

I also do not know if the column type I select for Meeting Duration can handle both a time format needed for Scenario 1 as well as be able to input “All Day” in Scenario 2

Any advice on how best to go about this?

Aleksi

Would it be easier if you use Time field for both Start time and End time? Then you would not need to think about the date change.

Skip2MiLu

Hi Aleksi

I not with you. Please elaborate on your proposal

Steve

Instead of:

Start Date (Date)
Start Time (Time)
End Date (Date)
End Time (Time)

Use:

Start Date & Time (DateTime)
End Date & Time (DateTime)

Skip2MiLu

I would also like to use the Calendar view. I thought you needed date and time to be separated? Or is there another way

Aleksi

Now when I read your post again, I probably understood your request wrongly, sorry about that. If you are using “Meeting Start Time” and “Meeting End Time”, why do you need to fill the “Meeting Duration” at all?

Skip2MiLu

In short, the users I am designing the app for think it would makes things easier the less they need to use their brain…

A example of “working forwards” inputing data in the form:

Meeting needs to start at 10/02/2020 and at 14:50
They need 1:15 to get though the agenda.
End Date spits out the same date in this case = 10/02/202 and more importantly the End Time calculates = 16:05

The idea is to be able to have a step up or step down increment button similar to that found in the number column type. I just need to have this working with time instead

Duration with a 15min increase or decrease step
So the initial value = 0:15 and the step up would go to 0:30 > 00:45 > 01:00 > 01:15
This will then shift the Meeting End Time accordingly

They then asked if the app is able work backwards using the same data as above.

Input Meeting End Date (10/02/2020) and Meeting End Time (16:05). As they know they have another appointment at 16:30.
They know they only need an 1:15 duration to get through everything.
So in this case the Meeting Start Times calculates backwards to = 14:50, and the Meeting Start Date remains the same = 10/02/2020

All this without needing to do much thinking from the user side in managing all the variables.

Maybe I should just concentrate for now in just managing time and leave date as there will only be a few instances where a meeting spans over more than one day and this might be a bit to complicated to work out how to manage those variables.

So this concept reminds me of my fuel expenditure app I downloaded from the play store (Sygic Fuelio)

You enter the litre of fuel you added to your car and the Price per L and it calculates the total cost.
Or you add the fuel litres and the total cost and it calculates the price per L.

I am pretty sure that this can be done with relevant expressions in initial values of relevant columns. Probably also might need make use of Reset If expressions.

I can see quite a few uses cases for this kind of setup

For example, they work a bit with product costing, pricing, gross margins, profit, discounts, markup’s etc
Take a look at this calculator online app, that demonstrate similar experience to what I am after
https://www.omnicalculator.com/finance/margin

This can calculate variable backwards and forwards…so to speak

Do you think appsheet has the capability to make the same kind of app?

I am thinking that I probably need to have a reset button somewhere as the app will probably not able to change values “on the fly” like the app sort of does.

It would be great if I can have the above experience and integrate it with other data in the app.
This is where app sheet is extremely powerful!

For example:
I have a list of products with the following columns and example values

Cost = $10
List Price (i.e Revenue) = $15
List Price Margin = 33.33%
List Price Profit = $5

I then want to see what Net price margins after discount will be at a given discount. Or maybe what discount can I give at a certain margin:

List Price Discount = 15%
Net Price = $12.75
Net Price Margin = 21.56%

So the idea is to play with different variable and the app calculates what is missing, when there is enough info added to do so.

I have no problem using DateTime using one column as Steve mentioned above. I just want to be able to use the Calendar view that seem to require Date and Time to be seperated.

Sorry for the long message, but maybe you have a better idea of what I wanting to achieve and to get an idea of what app design is possible to do in future.

Aleksi

How about if you use a simple number field with 15 increments and then you convert it back to End DateTime value when the Start Date and Time value is set (or the opposite)?

Skip2MiLu

It was one of my considerations.

Questions

How do I use date time in calendar view that needs date and time seperate or can I use the same datetime column for both start date and start time on the calendar view?

1.1. If not… must use an expression that splits datetime into two virtual columns? One for date and another for time and then use these in the calendar view?

1.2. What expression do I use for the virtual column if this is what you are suggesting?

Based on your recommendation how do I mange a number type when the increment goes beyond 60. Eg. 75 supposed to be 01:15 instead.

2.1. I created another two columns to experiment as using them as a datetime type.

Meeting Start
Meeting End.

What expression would I use that will calculate Meeting End from Meeting Start if the Meeting Duration is either of the following column types options
2.1.1 Duration
2.1.2 Number as suggested by you and factoring in question 1
2.1.3 Time

The following produced and error
[Meeting Start] + [Meeting Duration]

What am I missing?

Aleksi

You can calculate the duration from the number field like
FLOOR([DURATION]/60)&":"&RIGHT(“00”&MOD([DURATION],60),2)&":00"

Skip2MiLu

Thanks for the help, but the number type does not seem to be a good option as it gets a bit complicate to interpret how many hours and minutes something when you are input the value into the form without have a virtual column to check with the interpreted duration of the number.

Is then possible to calculate [Meeting End] from
[Meeting Start]+[Meeting Duration]

Where [Meeting End] and [Meeting Start] are a datetime column type and [Meeting Duration] is either a Duration or a Time column type?

Skip2MiLu

I was looking at the following help documents and notice them mentioning the following about duration.
Going to see if that make a difference.

Note that each of HOUR() , MINUTE() , and SECOND() accept as input a Duration value, not a Time value. To convert a Time value to a Duration value, subtract another Time value. For instance, to convert the current time-of-day to a Duration : TIMENOW() - "00:00:00" .

App that works "forwards" and "backwards" (This title should garner some interest!)