Expression test correct but not functioning in app

I have a table with a column [Dates of Service]

It as a virtual column of a list of related sessions with their individual [Date of Service] value

for my test I have two values 6/8/2020 and 6/15/2020

I created a behavior to update [Dates of Service] every time a new session is added

The expression for the [Dates of Service] looks like this

Expression

Text(any( top( sort(
select(ClaimSessions[Date of Session],[Claimnumber] = [Claim ID]), false
),1)), “mm/dd/yyyy”)

& “–” &

Text(any( top( sort(
select(ClaimSessions[Date of Session],[Claimnumber] = [Claim ID]), true
),1)), “mm/dd/yyyy”)

When I add the first session (be it 6/8 or 6/15)
Then the [Dates of Service] shows it accordingly. 6/8/2020–6/8/2020 or 6/15/2020–6/15/2020, depending which is added first.

When I add the second session it does not update the value.

I tested the expression with both values present the the test result showed
6/8/2020–6/15/2020 correctly.

I set the behavior to “display prominently” Thinking that maybe something was off between the time the session gets added and the app syncs.

With both values present, everything synced, and pressing the behavior is still does not update the value. It just shows the range with the first value that was added.

But again, when I test the expression it shows 6/8/2020–6/15/2020 correctly.

Any thoughts?

Thanks

Update,

Still have not found fix, but thought I would add some data that I am finding as try to figure this out.

Summary of what I found with my expression
top(sort(select()))
-in the expression test the top() is taking its values from the list produced from the sort()
top(sort(select())) as expected
-and in the app the top() is taking is values from the select() and then running a sort()
sort(top(select())) not as expected

Details

Summary

I added some values to my ClaimSessions table so that the the date of session values are as follows (in row order)
1–6/15/2020
2–6/8/2020
3–6/1/2020
4–6/5/2020
5–6/20/2020
6–6/16/2020

if I set the expression to

select(ClaimSessions[Date of Session],[_thisrow].[Claim ID] = [ClaimNumber])

I get

expressiontest=6/15/2020, 6/8/2020, 6/1/2020, 6/5/2020, 6/20/2020, 6/16/2020
appresult = 6/15/2020, 6/8/2020, 6/1/2020, 6/5/2020, 6/20/2020, 6/16/2020
Same

If change the expression to (adding sort)

sort(select(ClaimSessions[Date of Session],[_thisrow].[Claim ID] = [ClaimNumber]),true)

I get

expressiontest=6/20/2020 , 6/16/2020 , 6/15/2020 , 6/8/2020 , 6/5/2020 , 6/1/2020
appresult = 6/20/2020 , 6/16/2020 , 6/15/2020 , 6/8/2020 , 6/5/2020 , 6/1/2020
Same

If I change the expression to (adding a top 6)

top(sort(select(ClaimSessions[Date of Session],[_thisrow].[Claim ID] = [ClaimNumber]),true),6)

I get

expressiontest=6/20/2020 , 6/16/2020 , 6/15/2020 , 6/8/2020 , 6/5/2020 , 6/1/2020
appresult = 6/20/2020 , 6/16/2020 , 6/15/2020 , 6/8/2020 , 6/5/2020 , 6/1/2020
Same

But now it gets weird

I change the expression to (changing to top 3)

top(sort(select(ClaimSessions[Date of Session],[_thisrow].[Claim ID] = [ClaimNumber]),true),3)

and I get the following

expressiontest=6/20/2020 , 6/16/2020 , 6/15/2020
appresult = 6/15/2020, 6/8/2020, 6/1/2020
Not the same

I tried some other various combinations and got the same results

Is this a bug?

It’s not a bug, I’m pretty sure, but your description of everything is difficult for me to understand. Please provide the following screenshots:

  • The column list for the table from Data >> Tables in the app editor.

  • The column configuration screen for the Dates of Service virtual column.

  • The complete App formula expression for the Dates of Service virtual column. Please note, a screenshot is required to help; cutting and pasting the expression is insufficient.

  • The column configuration screen of the Date of Session normal column.

Sorry I realize it was a lot of data

For the sake of this discussion the i can produce my issue regardless of any virtual columns

My Data from the table ClaimSessions

Data

image

My Column list for table Claims

Claims

My Column list from table ClaimSessions

ClaimSessions

My Behavior to set the value

Behavior

The expression used to set the Value of [DatesofSession]

Expression

The results when I test the expression

Test Results

My View before running the behavior (put the “Before text” so it would show up)

Before

image

My View after running the behavior

After

This is just a sorted view of the first three rows returned by the select.

As another test I tried removing the select from the expression and just using a list that contained the values. Oddly it worked
Here are the details on that

Behavior expression without Select()

Just the list without the select

I get this test result

Test result

And the value is correctly evaluated in the app

Correct

2 Likes

Dates of Service must be of type List of Date, not Text.

1 Like

Thanks Steve,

I this afternoon I managed to get this working by coming at my end goal from a different angle.

Instead of trying to calculate one value to enter into my table I decided to add three virtual columns

1 The first date in the list ( type List of Date)

First

top(sort(select(ClaimSessions[Date of Session],[_thisrow].[Claim ID] = [ClaimNumber]),false),1)

2 The last date in the list (type List of Date)

Last

top(sort(select(ClaimSessions[Date of Session],[_thisrow].[Claim ID] = [ClaimNumber]),true),1)

3 At text sting that converts the values from the first two. (Type Text)

Final

text(date(any([FromDate])), “mm/dd/yyyy”) & " – " & text(date(any([ToDate])), “mm/dd/yyyy”)

When I add/delete a new value it does take about 10 seconds for the values to update, but I can live with that.

Thanks again