Filter by Dates a lot to Learn (Date Formated Text...

yastaaa · 08-13-2021 05:29 PM

Having trouble to filter by Date. Have a table Entries that has to be Filtered by three columns [Track] [Date] [Race] .

No problem with the other two columns but with date, we cannot filter.

When i display the data of the slice the DAte is expressed as a Value.

In my data is with text format DD/MM/YYYY

When i do filtering i have a column Racing Date that because of one reference One to Many brings a to column concatenate key instead of the date. So to solve that we created a virtual column for the filter with a formula to extract the date from as example : 8/12/2021

The formula DATE((ANY(EXTRACT(“DATES”,[Racing Date])))) This is the Test result.

THe filter is not working. Could be that expression result must be a Date VAlue? If so.

What is the right expresion to convert a Date to a value date.

WillowMobileSys

Usage of EXTRACT() is a bit overkill for your needs. Extract is meant to pull values(Dates in your case) out of a blob of text. [Racing Date] is already a Date so no need to use Extract.

First thing that jumps out at me is the date format. You want to be sure that all sources are using the same localization settings with regards to your dates.

Once this is established, there shouldn’t be anything special you need to do to be able to compare or filter by dates

So far what you have shown is not a filter. Where are you trying to filter by Date? Can you show a screenshot of that expression so we can help fix it up?

yastaaa

Thanks for this, new for me. We corrected all the tables and the Google Book. They had mixed locale settings . Maybe because i was using a VPN connection.

In my understanding, i thought that this was not important . Though that dates were always saved as a value and converted for display depending on your device settings, but inside the program it was allways a value. But seems that is not like i thought. We are still carrying ballasts from the computer past (Y2K and …Y2038).

WillowMobileSys

I am certainly not an expert but I think it depends on how the values are actually stored and retrieved. For example, if dates are written to a sheet column as text, how they are retrieved (and what internal value represents them) will depend on the format of the text. A text value of “12/8/2021” would be retrieved as December 8th in a US locale but for others that text value is retrieved as August 12th.

Were you able to get past your issue?

yastaaa

After doing all the locale formating for the colummn sheets, regenerating all tables involve, the issue is still on.

I can not filter because the dates are stored or processed as Values (meaning numbers). So i need to convert the date extracted to a value (44419). Is it any expression for that?

WillowMobileSys

I don’t think the “numbers” are the issue.

Looking deeper at your original post I have realized a subtle issue that is likely causing your problems.

You have Racing Date as a Ref column. Ref columns use Key values from the tables to store a reference to a row and since your screenshot shows the numeric representation of a Date, you probably have the Racing Date in that table as the key.

BUT…your table has multiple rows with the SAME date. Key values MUST be unique therefore Racing Date cannot be the key. There is probably an error being reported somewhere that multiple rows have the same key value and this is likely preventing the filtering.

If the above is correct, then I recommend adding a new column to your Races table for a Row ID - maybe call it Race ID and then use the UNIQUEID() function to assign the value and then set this column as the key. This will ensure you have unique key values for the table rows.

I think this will correct the filtering problem by Date

yastaaa

I am beginner but in my understanding . This is my guess shoud be the good practice.
I removed repeating the [Track] [Racing Date] [Race] in the child tables.
Racing Days is like the mother table with a CONCATENATE KEY _KeyRacingDays of [Track] [Racing Dates]

To create reference i have added virtual columns with the expression REFROWS to the Key of the Mother Table in the Child Table. I.E. REF_ROWS(“Racing Days”, “_KeyRacingDays”)

WillowMobileSys

Your table diagram is great. But it doesn’t seem to show all the key values so I’m not clear on the relationships.

Were you able to resolve the Date filtering problem?

If your relationships are created correctly, AppSheet will automatically create these “Related” virtual columns for you.

yastaaa

Maybe because i started from a different structure from google sheets those relationships were not created automatically.

One of the problems people may face with this aproach is when you create this structure when you create a form to add races you will have problems to identify the Track and the Date.

The Dropdown will bring only the date column of the concatenate key.

WillowMobileSys

It’s based on how the relationships are defined within the AppSheet tables. I am including an article below to provide more details

Actually the value shown in the dropdown is controlled by a property on the table called "Label’. You are seeing only the Date because it is marked as the Label column. Switch it to your concatenated column, and those values will be shown instead. See image below where to find the Label property.

NOTE: You can only have a single column marked as the label…UNLESS you are also including a column with an image. In that case the Image is shown first and then the second data value column is shown.

I hope these help!!!

yastaaa

This worked for extracting the date and it is a simple expression. Thanks to Steve Steve Coile

WillowMobileSys

Yes. In a concatenated field, EXTRACT() works great to pull out the date portion from the text blob. But your expression above was using [Racing Date] directly. No need for EXTRACT here - that value is already a date.

yastaaa

Thanks for the help.

This thing happen when you mark two columns as label.

This thing happen when you mark the key and is hidden.

So to correct this mark the key as label and remove the other ticked as label, make sure of this. Edit the key columm and tick Show? Then everything will be Ok.

WillowMobileSys

Right. the only time you can mark two columns as Label is if one of them is an Image. then the image is shown first followed by the value of the second column.

Labels cannot be hidden. They are what is intended to be displayed instead of the Key column value

Filter by Dates a lot to Learn (Date Formated Text or Value Date for Filtering)