Link StartDate to another non-referenced table and display it as an appointment in the calendar

AppSheet AppSheet Q&A
,
1

Hello everyone,

For quite some time now, I have been trying to display a date that must be assigned in advance in a calendar view.

Here is my current data structure.

DATENSTRUKTUR - IS
DATENSTRUKTUR - IS2266Ă—1133 147 KB

And here is the connection I am trying to achieve.

DATENSTRUKTUR - SHOULD
DATENSTRUKTUR - SHOULD2266Ă—1133 164 KB

These two columns (highlighted in green) in the respective table should be linked to each other and displayed in a calendar view. How do i connect TRAINEE[TRAINEE_START_DATE] with TOPICS[DUE_WEEKS_AFTER_TRAINEE_START], that i can see the actually date for the instruction in a calender view? The view is preferred as Dashboard with TRAINEE_TABLE + TRAINEE_details + calendar view. As soon as i select a TRAINEE in the table VIEW, it shows me the TRAINEE_details view, but not the calender view entries.

I have already tried the following.
I created the virtual column in TOPICS[v_TOPIC_DUE_DATE] and entered the following formula.

WORKDAY(
  ANY(SELECT(
    TRAINEE[TRAINEE_START_DATE],
    [ID] = CONTEXT("ParentRow")
  )),
  [DUE_WEEKS_AFTER_TRAINEE_START] * 7
)

I then created a SLICE in TOPICS and set the row filter condition as follows.

AND(
    ISNOTBLANK([v_TOPIC_DUE_DATE]),
    ISNOTBLANK([DUE_WEEKS_AFTER_TRAINEE_START])
)

Then I created a calendar view on the slice and selected the date calculated in the virtual column as the start date and the topic as the event. Finally, I created a dashboard containing the TRAINEE table, the TRAINEE details view, and the calendar view of the slice in that order.

The details view displays correctly as soon as I select a TRAINEE.

image
image2551Ă—1304 359 KB

Nothing is displayed in the calendar view, or rather, a date of January 1, 1970, plus the corresponding week is added. This shows me that the formula for obtaining the date does not recognize the selected TRAINEE, because 1.1.1970 is 0 for a UNIX Date.

What am I doing wrong? How can I calculate the date for the instruction and display it in the calender view as soon the TRAINEE is selected in the dashboard?

Thanks in advance

2

Too much info, so I just skimmed it.

A slice contains rows. A view displays rows. If the slice used by the view contains no rows, the view will be blank. Does your slice contain any rows?

1 Like
3

As soon as I click on TEST in the formula input in SLICE, I am shown all rows that contain the desired values. However, the following is displayed as the date in TOPICS[v_TOPIC_DUE_DATE]:

12.09.0001 (or later depending on the count of weeks)

Could it be that the date 19.01.0001 means that the formula works syntactically, but no valid start date is found in TRAINEE[TRAINEE_START_DATE] because CONTEXT(“ParentRow”) is empty (because the calendar view is not integrated as a subordinate view of the trainee’s detail view)?

4

Exactly.

BTW, there’s no such thing as CONTEXT("ParentRow").

5

Okay, thank you Steve for clarifying that. I think I got the wrong approach from the AI.

How can I achieve this, as defined in green in this overview?

DATENSTRUKTUR - SHOULD
DATENSTRUKTUR - SHOULD2266Ă—1133 164 KB

I would also appreciate any tips on the right direction to take, so I know what to research.

Thank you in advance.

6

Stop describing your problem in terms of your tables. Explain the problem in plain English (or your native language), then explain how your tables relate to your goal.