How-to questions

Hi! Don’t forget to take a look in the Knowledge Base for answers to common questions :)

  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. Count working hours

    I need to count working hours in the range defined by "Start" and "End" fields. Both fields are Date and Time format. The working day lasts from 7 AM till 3:30 PM. I need to count time only from this range. Overtime and weekends should not be included. I can start and end work any day at any time.
    weekdays() function counts only days, datedif() function includes all 24 hours and weekends.
    Any ideas?

    3 votes
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  2. sum a specific column when the child record is in a specific location

    I have a table where users can select a parent record in more than one location. For example, a child table, "work order" contains both "Service 1", and "Service 2" links to the "services" table. Each linked service has an associated quantity column in the "work order" table. I have tried using the sum function in the "services" table to sum the total services entered but when I sum the "service 1 quantity" all of the values in that column are added regardless of whether the service was a parent record in the "service 1" link or in the "service…

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  3. Calculating Task Completion Time

    I am looking for a way to capture how long it takes a user to create a record.
    Does TrackVia stamp when a Form is opened? If so, then I can use this value in a datedif formula against the record Create Date/Time to see how long it took to open the form, key in the information, and click Save.

    4 votes
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  4. Is it possible to add comments when writing a long formula?

    In most languages you can add comments that does not affect the code or formula. Out of habit I tried to use ' but that didn't seem to work. Does trackvia allow for this kind of thing and if so what is the symbol or text?

    E.g.

    If({Field} = 1, A + B, A + C)

    'comments about above statement

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    2 comments  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  5. We use TrackVia as a check register, and need a warning message that says "Are you sure you want to allow Sum of Balance value to change?"

    We use TrackVia as a check register, and need a warning message that says "Are you sure you want to allow Sum of Balance value to change?"

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  6. API and Calculated text field

    Hi we have a table that has calcualated fields, we are importing data into the table via API but the calculated fields are not calculating? Does anyone have an easy fix

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  7. 1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  8. 1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  9. Show a calculated date only using WEEK days

    Show a calculated date only using week days, so two days from Thursday would show the Monday's date, so today is Thursday the 13th July 2017, I want to show the calculation of two additional week days, so present Monday 17th July 2017

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  10. can any one help me Calculate working week-days?

    I am trying to calculate the number of working week-days from two dates, at the moment it is giving me the full 7 days per week.

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  11. Count the number of child records with specific data in a field

    I have child records with "Available" as a field. I want to count the number of records that contain "Yes" in that field and ignore the rest.

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    2 comments  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  12. 1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    0 comments  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  13. How do I calculate the sum of a column so that I can then use that to determine the percentage of the total for a given value?

    For example I want to show the percentage of total sales by store, salesperson etc.
    Everything I have tried so far just returns 100% as it is only doing the calculation on a row by row basis rather than using the column total.

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    2 comments  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  14. Calculate Remaining Balance

    I have a view where I can see a client's Total Cost, Deposit they paid and their Remaining Balance (I have Balance set with the formula Total Cost - Deposit.) When a client wants to make a partial payment, how can I do that and have the Remaining Balance calculate that?

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  15. autogenerate a record in a parent table using the latest value introduced on the child table?

    I have different prices per product and I want that on the product table the final price would be the lastest price introduce in the price table )child to product)

    5 votes
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  16. calculate the total amount from a column, not a count of what is showing information?

    I would like to be able to calculate a total in a column, i.e. column shows amount of Disposition, would like a sum at the bottom showing the total of all those claims.

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  17. Is there a maximum number of IF statements I can nest?

    I ask this because when I go to save the calculated field a dialog box appears stating: "We've had a minor problem. We can't tell if your request completed. Please click OK below to refresh your browser." Prior to this dialog box appearing, however, a string of text appears (stating: "Large tables usually save in under a minute. Feel free to leave this page, we'll let you know when this is saved") atop the whited out page as the calculated field is saved - see attachment "Problem with IF Statement."

    Also, once I paste the copied IF statement formula into…

    1 vote
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  18. Auotnumber and Text in calculated text

    Can anyone please explain why this calculated text field formula works when Historic Order Number has a value but not when it's blank. I'm guessing it's something to do with the fact that Historic Order number is a text field whereas Order Number is an autonumber, however I can't find a to string function.

    IF({Factory}="Toplink", "TLKNZ", IF({Factory}="Playmore", "PKNZ", IF({Factory}="Colaro", "CKNZ", IF({Factory}="Vithal", "VKNZ", "")))) & IF({Historic Order Number}="", {Order Number}, {Historic Order Number})

    3 votes
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    2 comments  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  19. Calculating Duration of Time

    Is there a way to calculate Time, (not Base-10 numbers)?

    I have a user who tracks Total Run Time (TRT) for TV Programming, and we need to sum/add the durations of multiple TRT's in the format of HH:MM:SS (Base 60).

    4 votes
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    0 comments  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
  20. Day of the Year

    Is there a formula to calculate the day of the year?
    Example: Today, 3/18/2015 is the 77th day of 2015.

    0 votes
    Vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    2 comments  ·  formulas  ·  Flag idea as inappropriate…  ·  Admin →
← Previous 1
  • Don't see your idea?

Feedback and Knowledge Base