Carry a field value until a new one is entered.

For HanDBase related topics that are not specific to one platform.

Carry a field value until a new one is entered.

Postby shawnjw » Thu Apr 12, 2018 9:25 pm

I am trying to make a database that can calculate my employee bonus daily and show it to me in a form. I have the form and the rest of the method figured out.

The problem I am having is trying to carry the bonus period start date forward on each record until I enter a new one. Each period is 28 days the last one starting on March 26th. I don't mind using multiple fields to make it work. Is there a formula I can use that will reference the last records value until a value larger than last records is put into a separate field?

Posts: 1
Joined: Thu Apr 12, 2018 8:53 pm

Re: Carry a field value until a new one is entered.

Postby ddhsoftwareadmin » Sun Apr 15, 2018 8:30 am

Hi Shawn,

Your question is a fun puzzle to solve. I think it can be done with multiple fields, particularly some conditional fields. So the first assumption is that you have a date field called start date. Then you need to grab the previous start date and put it in a field. You can use a calculation with the value of previous start date + 0 outputted as a date (we will call this prev start date). You may wish to hide this field from view.

Then you should have a conditional field type- it would have this setup: Field name: New Start Date. Formula: if start date > prev start date, output is start date, else output is prev start date.

This *seems like* it would give you the result you wanted, but we've actually made a few mistakes in the above.

The first is that we're taking the previous start date value rather than the output of this conditional field. So if you had three records and the first two had:
No Date

as the date field, record three would show No Date as the previous start date and you want it to show 2/10/2018. So change the calculation to take the previous record's output of the conditional field instead.

The second issue is that while a date field and a calculated field can treat a date value internally as numeric (for comparisons), the output of a conditional field is a text output. So if I say if date1 > date 2 output is date 1, the output of the value will show as date 1, but this can't be used by another calculation later on because that date value is not a text value, not a numeric one. That means the 'fix' above won't really fix things. This can be worked around, but it requires a complex change in what we've set up:

We need to store the date when we do the calculations as an integer, not a date. Thankfully HanDBase internally stores a date as the number of days since 1/1/1971 already so we just need to create a calculated field that does date + 0 and outputs as an integer. So we add three fields:

Start Date as Int: Calculated = Start Date + 0 output as integer field.
Prev start as int: Calculated = Previous "New Start date int' + 0 output as an integer field.
New Start Date int: Conditional = if "Start Date as Int" > "Prev Start as Int" output is "Start Date as Int", else output is 'Prev start as int"

So now, the "New Start Date int" should always match the value you're looking for, as it does the same calculation as originally but instead doing it on integer fields. These fields will carry over from record to record unchanged.

The problem now is that the numeric value of the date result is not that useful to you as a user- you want to see a date value. The solution there is simple- add another calculation field that takes the numeric output of the above and changes it to a date format. the formula would be "New Start Date Int" + 0, output as date field.

Once you are done with the above you'll be able to clean up a bit:
You can remove the prev start date field, as we're not using any more.
You can remove the New Start Date field, as we're not using either.
(To remove, you just set the field type to 'Not used')

You can hide the following intermediate fields that serve no purpose to the end user of the database:
Start Date as Int, Prev Start as int, New Start date int
(To do this, toggle off the 'Visible in Edit record screen' option, and lower the % of List View to zero).

There you have it- you now have a working solution!

I've attached a database demonstrating the above for you. I have not removed the fields we didn't need or hidden the intermediate ones so you can see how they all work!
(2.08 KiB) Downloaded 66 times
Site Admin
Posts: 386
Joined: Tue Dec 09, 2008 2:13 pm

Return to HanDBase General Discussion

Who is online

Users browsing this forum: No registered users and 2 guests