How to add ref fields in a row and set initial values?

[Marcel1]

Hello,

It might be a simple thing but I can’t figure out ‘how to’ of 'what I might do ‘wrong’ So any help will be welcome. There are two questoins I have.

Question 1.
In the table ‘timeregistration’ I have several fields of the ref type that refer to an other table which provide the time (type=duration) they can select.
To be more specific:

In the table ‘timesheet’ a user can registrate time for:
Intake,
Consulting,
Reporting.

These field are of type = ref.
The reference table is ‘timeunits’ (read-only) and has a column ‘time’ of type = duration. Is it like:
000:00:00
000:15:00
000:30:00
etcetera.

So users can only select one of those times for Intake, Consulting and Reporting.

In the table ‘timesheet’ I also have a field ‘Total time’. This must be the sum of Intake, Consulting and Reporting and being stored in the table ‘timesheet’.

I tried many things (like with SUM / SELECT) but just can’t figure out how to make this work.

Question 2.
I want the initial value of the fields Intake, Consulting and Reporting to be the value of the first row of the column ‘time’ in theref table ‘timeunits’. In this case 000:00:00 .It has as Key number 0.
No luck so far on my attempts.

Kind regards,
Marcel

[Steve]

For question 1, you cannot use SUM() with Duration values. You’d be better off recording the time units as number of minutes as a Number value (e.g., 0, 15, or 30) rather than as a Duration value. Otherwise, you’ll have to convert the individual Duration values to a Number of minutes, sum them, then convert back to a single Duration.

For question 2, have you tried LOOKUP()?

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[Marcel1]

Hello Steve,

Thanks for your time and thinking along with me.

About question one:
I did use a SUM() on fields of the type duration in an other app for testing purposes. That worked fine. In this app have in the table ‘timesheet’ the fields:
Intake
Begeleiding

They are of the type duration (users can freely fill in the time).

The field ‘total’ in the same table ‘timesheet’ which is also of the type duration sums the duration of the two fields Intake and Begeleiding by the code in the App formula:
SUM(
(LIST(
[begeleiding] ,
[intake]
)
)
)

This works fine with duration values, so why not when the field is op the type ref ? Because it is of the type ref and not duration?

Of course an other solution, if there is any, would also be welcome.
My goals is just that users can only select predeterminant times (durations) preferable from a reference table, and that I can sum them in a field ‘total’ in the spreadsheet.

About question two:
It doesn’t seems to work? I get the error:The expression is valid but its result type ‘Duration’ is not one of the expected types: Ref

Kind regards,
Marcel

[Steve]

Ah, okay. My mistake!

Perhaps SUM()'s behavior has changed since I last tried with Duration values. Sorry for the confusion.

That you’re using Ref values with SUM() instead of Duration values is indeed the problem. A Ref value is a “reference” to another row. Using a dereference expression, you can access values in the referenced row. If I’ve interpreted your table structures properly, you could use this expression:

SUM(
  LIST(
    [begeleiding].[time],
    [intake].[time]
  )
)

Note that the Total time column should be of type Duration.

[Marcel1]

Hello Steve,

In your solution it presumes that data is being stored in the table ‘time’. But that not the case. The data in this table is only a set of durations a user must be able to select in the table ‘timesheet’.

However, I just dit some more logical thinking and experimenting and found the solution to meet my wish.
In the table ‘timesheet’ I set the type of the fields ‘Intake’, ‘Consulting’, ‘Reporting’ and ‘Total time’ to ‘duration’. So ‘Total time’ can be calculated as a sum of ‘Intake’, ‘Consulting’ and ‘Reporting’.
Further more I set for those three fields the Suggested values and Valid if to: timeunits[time].

Now the user can select the times from the column [time] as read-only.
To set Valid if makes it to be read-only and not being able to add any times themselves.

Kind regards,
Marcel