How to calculate with relational fields

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

How to calculate with relational fields

Postby kluesi » Tue Jun 19, 2018 9:58 am


I have two databases (tables). One with data "A" and another with data "B". There is a one to many relation between A and B. One header dataset in A have multiple datasets in B. Each dataset in B has got a time value for the duration of a process. The datasets in B are sorted with a "Pos" attribut. In each dataset I have a calculated field "sum_duration" for the sum of the "duration" of this dataset and "sum_duration" of the previous dataset. So I get a cummulation of all the durations. This works fine.

From database "A" I get the header-dataset with the startdate and starttime. I inserted a "relationship" field in database B and get these two values. Now I want to add this date and time (A) to my durations (B). I see the right date and the right time in each line (B) but if I calculate a sum with duration and this relational field I get wrong sums. Eg. for the date field. I have a date value "19.06.2018" and I add this with the relational field I get a sum of "20.01.1904". This is because the date 0 = 01.01.1904 and the db interpretes the date "19.06.2018" only as 19. So 1+19=20 and 20 is the date "20.01.1904". But how can I tell the db that this relational field is a date?

By kluesi
Posts: 3
Joined: Tue Jun 19, 2018 9:23 am

Re: How to calculate with relational fields

Postby ddhsoftwareadmin » Tue Jun 19, 2018 8:55 pm

Hi there,

Thanks for your post and great question. So HanDBase treats relationship fields as strings or text, and the process of converting a text value to a numeric is to take the numeric portion up until the first non-numeric, hence seeing what you are seeing. There is a workaround though and here is what I usually recommend:

- Create a second field in the database where the duration or time field you want to have treated properly in a calculation. This field will be a calculated field with the value of 'Time field' + 0. The output format of the field will be an integer. This will output the number of seconds from the beginning of the day. You can set this field to be 'not visible', and 0% of the list view so that it's not visible to the user. But this field will be the one you use in the other table to do calculations on. You can use the same relationship setup you have for the time/duration field itself, and HanDBase will grab both related values at the same time, the one that you display and the one that you use for calculations.

Hope this helps!
Site Admin
Posts: 444
Joined: Tue Dec 09, 2008 2:13 pm

Return to HanDBase General Discussion

Who is online

Users browsing this forum: Bing [Bot] and 2 guests