Discussions

 View Only
Expand all | Collapse all

Split Formula Duration field into Hours and Minutes

  • 1.  Split Formula Duration field into Hours and Minutes

    Posted 03-07-2020 02:21
    I have a [Total Time] (Formula Duration) field.  I am using pipelines to send the data to Quick Books Online.  QBO wants the hours and minutes separated.  How can I split the [Total Time] into 2 fields [Total Hours] and [Total Minutes].

    [Total Time] = 2:15

    I need [Total Hours] = 2
    and [Total Minutes] = 15

    ------------------------------
    Mark Comish
    ------------------------------


  • 2.  RE: Split Formula Duration field into Hours and Minutes

    Posted 03-07-2020 16:56

    Mark,

    Congratulations on Pipeline access.  You are among a select few to have the tool set.   Please do a few posts on how it works and how easy or hard it is to work with.


    Total Hours is a Formula Numeric
    ToNumber(Left(ToText(ToHours([Total Time])),"."))

    Total Minutes is a Formula Numeric
    ToNumber("." & (Right(ToText(ToHours([Total Time])),".")))*60



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 3.  RE: Split Formula Duration field into Hours and Minutes

    Posted 03-07-2020 19:00
    Thank you Don!  Worked like a charm.

    So for I have built Pipelines for Quick Books Online and Mail Chimp.  I have had great success with building from Quick Base to Mail Chimp or Quick Base to Quick Books Online.

    I have not had good success going from Quick Books Online to Quick Base or from Mail Chimp to Quick Base.  I have a support ticket in as it won't trigger in this direction.  It has been several days and no solution yet.

    On the Pipelines that work they are great.  Takes a little effort to get the hang of it but I really like them.  Right now I have the following working...

    1. Update a contact in Quick Base and then a pipeline to also update the record in Mail Chimp (something I could never get to work with Zapier.  Zapier only worked with a new record in Quick Base.

    2.  When a time log is created in Quick Base it passes this info to Quick Books Online and creates a "Time Charge"

    Still lots of pipelines I want to build once they are working in both directions better.

    ------------------------------
    Mark Comish
    ------------------------------



  • 4.  RE: Split Formula Duration field into Hours and Minutes

    Posted 03-07-2020 19:22
    Mark. We appreciate the Pipeline info.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Split Formula Duration field into Hours and Minutes

    Posted 03-08-2020 09:28
    Mark,

    Thanks for the technical info.  

    I have another request.  How is the usage, pricing, limits laid out?   I have no sense yet of what to tell customers Pipelines will cost them or how to budget.  If Pipelines reaches its potential and makes it very, very easy to share data, will it blow up the bill.

    Have you ever been to a great Dim Sum restaurant?  Suddenly you need a mortgage to cover all the things you grabbed.

    Would love to know what you are seeing about this side of the product.



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 6.  RE: Split Formula Duration field into Hours and Minutes

    Posted 03-08-2020 13:00
    I am on the Premier Plan.  I believe we get 3 integrations in our plan.  I am not sure the cost of adding above that.  You can build all the Quick Base to Quick Base pipelines you want but they do count as "Steps" when they run.  We get 2000 "Steps" per month.  It is basically the number of times we run a pipeline.  It is a bit confusing as it can be more than 1 "Step" each time a pipeline runs based on things it does.  Sorry to be so vague.  I have run 200 "Steps" in 10 days so the number seems like it will be enough for my use.  Let me know if there is anything else I can share.

    ------------------------------
    Mark Comish
    ------------------------------



  • 7.  RE: Split Formula Duration field into Hours and Minutes

    Posted 03-09-2020 09:52
    Edited by Austin K 03-09-2020 09:55
    I haven't had a chance to build out any full Pipelines of my own yet but there are a few running from other users. The major problem I've had so far is that Pipelines seem to be a personal thing and cannot be shared? I didn't even know another user had them up and going until I saw the step runs going down and asked around the office. It only shows you Pipelines you have made so you cannot collaborate. Also if the user that made those Pipelines are gone that day you seem to just be SOL and need to wait for them to be back to make any changes for that reason. Of course if you have a single service account you do everything with this isn't an issue.

    It is annoying though because when I look at usage I see that the step runs are down but under activity nothing is shown because it isn't my Pipelines doing this. So I can't even know what is using step runs on the account. I don't know if there is a certain level of access required to see every Pipeline or what the deal is but obviously mine isn't high enough. I'm not the realm owner but I am admin on the realm.

    I'm seeing the rate at which step runs get used as a big potential issue. They are split into 65 day time chunks for me. I'm not sure if everyone gets the same step runs but we got 250k over 65 days and they go FAST. Like I said above I don't know exactly how many Pipelines are running on the realm but we have used almost 25k step runs in about 10 days and I am fairly sure we are barely using Pipelines. Moving some records between some tables mostly right now as I understand it.

    Considering we are mostly just testing it out right now and not doing anything critical I feel like the usage is going to get wild once we start to fully utilize Pipelines. A step run is every single little step that happens in your Pipeline. So every thing you add to it is a step run for every single record it touches. If you have 100 records that will be filtered and then a loop of 5 actions in there for each record that is going to be a minimum of 500 step runs for that as I understand it. 

    As far as pricing goes, I wish they laid that out. All I know is we get the 250k step runs for 65 days and after that it says we need to contact them for pricing. So no information is being released on that. I am VERY curious what the pricing costs beyond the step runs given to us because of how fast they go. I am worried the pricing may end up being pretty high since they don't list it.


  • 8.  RE: Split Formula Duration field into Hours and Minutes

    Posted 03-09-2020 10:27
    Edited by Don Larson 03-09-2020 10:27
    Austin,

    Many thanks.   This cuts to exactly the concern I am hearing from customers.

    A complex PHP script might be $1000 to $2000 to have a developer build it. However running it is essentially free with the caveat that there is some cost for API calls.

    A Pipeline is essentially free to build because the Citizen Builders time is all in SGA but if your Pipeline then costs $1000 a month to run it, hiring a PHP developer is a total no brainer.  It might even make sense to go to code school.  

    I sense that Pipelines will be very usefull.  Hopefully the pricing will transparent and easy to explain to the customers because that is the advantage of standard development, $X  for the code and done.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 9.  RE: Split Formula Duration field into Hours and Minutes

    Posted 03-09-2020 12:57
    Edited by Evan Martinez 03-09-2020 13:21
    Hi Austin,

    The ability for admins to see other Pipelines on your realm and to even be able to collaborate and change those pipelines is feedback our PD team is absolutely listening to and thinking about. We have a User Voice thread on it with feedback from Pipelines users and just a few days ago Gev with our Product Team posted some questions to gather feedback on the needs and the experience users are looking for with being able to see and interact with all their Pipelines. You can find that thread here:
    https://quickbase.uservoice.com/forums/111823/suggestions/39818245

    I would recommend going in and adding your vote and any specific feedback or needs you have around the visibility of Pipelines as it is something they are taking a look at right now and planning for. Thanks so much for your feedback.

    ------------------------------
    Evan Martinez
    Community Marketing Manager
    Quick Base
    ------------------------------



  • 10.  RE: Split Formula Duration field into Hours and Minutes

    Posted 08-21-2020 14:29
    Thank you Mark. I was looking for this as well. However, I need to add seconds as well and so have also created a pure StartDate and End Date and Start Time and End Time fields in Date only and Time only formats as the Date-time format does not support seconds. 

    It appears the rounding in minutes is throwing my duration off.  So if my Start Date and Time is 07-03-2020 23:02:30 and End Date and Time is 07-04-2020 06:03:05, i got my hours to calculate as 7 hours, but minutes should be 0 and seconds 35. Not getting that! 

    Can you help?


    ------------------------------
    Deepa Deepa
    ------------------------------



  • 11.  RE: Split Formula Duration field into Hours and Minutes

    Posted 08-21-2020 15:31
    Oh! I figured it out. Just needed to clear my head :-)
    Duration (hrs) - ToNumber(Left(ToText(ToDays([10. Duration (Date/Time)])*24),"."))
    Duration (mins) - If (Minute([10. End Time])<Minute([10. Start Time]) ,Minute([10. End Time])+60-Minute([10. Start Time]),Second([10. End Time])<Second([10. Start Time]),Minute([10. End Time])-Minute([10. Start Time])-1)
    Duration (secs) - If (Second([10. End Time])<Second([10. Start Time]),(Second([10. End Time])+60)-Second([10. Start Time]),Second([10. End Time])-Second([10. Start Time]))

    ------------------------------
    Deepa Deepa
    ------------------------------