Expand all | Collapse all

Formula Help

  • 1.  Formula Help

    Posted 05-22-2019 14:01
    I need help putting a formula together where i would like to divide a given number by the number of details. Can anyone help me find this function / formula?


  • 2.  RE: Formula Help

    Posted 05-22-2019 14:08
    Are you saying that you have a Parent record and it has a child table in a relationship called details?

    If so you can make a summary field on the left side of the relationship to count the # of Details.  You might chose to call it

    # of Details

    Then the formula numeric field on the on the parent record would be

    [My Given Number field] / [# of Details]

    and set the decimals to display as say 0 or 1.

  • 3.  RE: Formula Help

    Posted 05-22-2019 14:23
    There is only one single table. will i need to create a child table to do this?

  • 4.  RE: Formula Help

    Posted 05-22-2019 14:24
    Can you explain what you mean by the # of details?  Can you tell us what the data in your table really is, in real life?  What do the records represent?

  • 5.  RE: Formula Help

    Posted 05-22-2019 14:45
    I will give it my best shot. lol...

    I am trying to track Daily output on a production cell based on tracking Downtime. Example: If we enter 10 minutes of downtime, we can calculate the estimated number of loss production.... 

    I would like a graph to show what our estimated output is for the day. so if we are expected to make 240 parts per day (10 parts per hour)  and we have 1 hour of downtime, i would like a bar graph to show estimated output to be 230 parts for the day. 

    The problem i am running into is that when i select "Summed" for my data value, it is calculating 240 parts for every entry of downtime and when i select average, i am not getting a realistic number for that day.

    What i think i need to do is lock the daily production number within the formula or divide by the number of entries.

    I hope this makes sense....

  • 6.  RE: Formula Help

    Posted 05-22-2019 14:49
    is your single table only downtime entries or are you also entering production values for each day into the same single table.

  • 7.  RE: Formula Help

    Posted 05-22-2019 14:50
    only downtime

  • 8.  RE: Formula Help

    Posted 05-22-2019 14:59
    The formula for expected production would be

    240 - (10 * [down time hours])

    But in a single table app you would need to enter a downtime entry for every day even it's is zero.

    If you did not want to do that, then there is a slightly more complicated two table design where you would not have to enter zero values for the "good days" when there is no downtime.

    In that setup, you would use excel to load up all the days for say the next 5 years.  You would create a date field in Excel for those days and import that column into a new table called production days.  You would delete out or not upload the weekend and holidays.

    The you would set the Key field of that table to be the date field.

    Then you would make a relationship to the downtime table  and use the date field as the reference field to join the tables together. But it will actually work better if you let the system create a new field called Related Date and then use that field but change it to be a formula field equal to the manually entered date field.

    Then make a summary field of the downtime hours and then do your calculation of expected production by day on the Production Dates table.

    Post back if you get stuck anywhere.

  • 9.  RE: Formula Help

    Posted 05-22-2019 15:11
    This is how i thought it would work as well but i am still getting off the chart numbers when i select Daily output is summarized by "Summed". It seems like the calculation is multiplying on every entry. if i switch the "Summarized By" to Average, then i am closer to the correct output but it is still not quite right. is there a way in the formula to say 240 on a given day or something like that?

  • 10.  RE: Formula Help

    Posted 05-22-2019 15:13
    If you have multiple entries per day for downtime, then you will need to have an app with two tables as I described above.

  • 11.  RE: Formula Help

    Posted 05-22-2019 15:16
    OK. we do plan to enter production numbers at some point and the way you described would fit our process perfectly. however this is phase 2 so i will just leave this report out for now until we are ready to start entering production numbers. thanks!

    you were very helpful as usual.