Create a form to select rows between 2 dates

[Tritonos_Gruppe]

Hi, I would like to create an form page in my app that alows user to select “From Date” to “To Date” and find all rows in between those dates. Then I want to create an report. I got most down except expression to select rows between to dates. Hope someone could help me in the right direction.

[Suvrutt_Gurjar]

Hi @Tritonos_Gruppen,

Welcome to AppSheet community!

Could you please take a look at this sample app that is similar to your requirement. Of course selection criteria of rows is some text values instead of dates. But the approach is similar to your description.

https://www.appsheet.com/samples/Reports-that-users-can-customize?appGuidString=69a02679-bfe6-45fa-8...

[Tritonos_Gruppe]

Hi @Suvrutt_Gurjar

This is what I am looking for yes, found this example but struggling to use dates instead. Could you help me?

I have on column that I would like to use so I can filter rows between 2 dates.

[Suvrutt_Gurjar · Accepted solution]

Hi @Tritonos_Gruppen

Is your data from where you wish to select the rows in different table than the table where your form is based on?

Ifyou are building the app exactly like the sample app, then you could use an expression something like
below for the VC [Matching Items] as in sample app

SELECT(Yout Data Table Name [Key Name of Data Table], AND([Date in Data Table] >=[_THISROW].[From Date], [Date in Data Table] <=[_THISROW].[To Date]))

[Tritonos_Gruppe]

Hi
Thank you so much, I was missing <= sign

[Mike_T]

Can I compare a date from current form using [_THISROW].[START DATE] to a date column in another table?

[Steve]

Yep. You’ll probably want to use LOOKUP() or SELECT(). More details about your problem will help us provide better suggestions.

See also:

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[lefarole]

@Suvrutt_Gurjar

Thank you very much for your information, it was very useful to me and I solved my problem.

Greetings.

[Admin_Agrotec]

Hello @Suvrutt_Gurjar , I tried your SELECT statement but get an error; > Arithmetic expression ‘([Hora Fecha Inicio] >= [_THISROW].[Rango Fecha Inicio])’ does not have valid input types;
This is my expression: SELECT(registros [Punto de Muestreo], AND([Hora Fecha Inicio] >=[_THISROW].[Rango Fecha Inicio], [Hora Fecha Inicio] <=[_THISROW].[Rango Fecha Final]))

[Suvrutt_Gurjar]

Admin_Agrotec:

[Hora Fecha Inicio] >= [_THISROW].[Rango Fecha Inicio])

Any math comparison ( greater/ less than) will need that the columns can be compared on both sides. So both the columns need to be of types such as DateTime, date, number, decimal, etc. Please ensure both the columns are of the same type.

[Steve]

Please post a screenshot of the entire error message.

[Admin_Agrotec]

Thanks @Steve I figured out. It wasn’t referencing the key column in the table.

SELECT(registros[ID Muestreo], AND([Hora Fecha Inicio]>=[_THISROW].[Rango Fecha Inicio],[Hora Fecha Inicio]<=[_THISROW].[Rango Fecha Final]))

Type Details>Element type = Ref
Element type details>Referenced table name = registros

Thanks…

[furkizio]

Hi @Steve

I have created a select statement for suggested values. I know that my conditions are meeting at least one row however I cannot see in suggested values. My order date is today.

Can you please comment? Thank you indeed

SELECT(
Marketplace Campaign[Campaign ID],
AND(
[Start Date]<=[_THISROW].[Order Date],
[Finish Date]>=[_THISROW].[Order Date],
[Category]=[_THISROW].[Category],
[Marketplace]=[_THISROW].[Marketplace]
)
)

[Steve]

Your expression looks okay. You’re using this as a Suggested values expression?

[furkizio]

I am using this one in suggested query, and the same with ANY function in initial value field. Please see below

[Steve]

In Expression Assistant for the Suggested values expression, try using the Test button. Do you get the results you expect there?

[furkizio]

It is matching my test order line, but not showing during line creation

Once line is saved and app is synced then if i edit the line, i can see in the list.

[SkrOYC]

I have nothing to say more than encourage you to use FILTER instead of SELECT() since you are taking the ID

help.appsheet.com

FILTER()

Select rows of table or slice

FILTER(
  "Marketplace Campaign",
  AND(
    [Start Date]<=[_THISROW].[Order Date],
    [Finish Date]>=[_THISROW].[Order Date],
    [Category]=[_THISROW].[Category],
    [Marketplace]=[_THISROW].[Marketplace]
  )
)

[furkizio]

Thanks for suggestion but no value.
Problem is due to dates because i removed date conditions and just work perfectly.
Order Date is a fetched via [Order Id].[Order Date] so there should not be any problem with this.

What should be date format in the gsheet? does it effect results?

[Steve]

furkizio:

What should be date format in the gsheet? does it effect results?

It certainly can, yes. If you remove the date conditions from your Suggested values expression, does it give you options in the form?

[furkizio]

Yes it is giving me result and value is selected automatically as well.

[SkrOYC]

Can you post the View Data view inside AppSheet for your table?

[furkizio]

Thank you for this question. I checked one more time all 4 fields in tables and found the issue.
It is fixed. Thanks

[SkrOYC]

Can you point us to the issue you were having so that anyone in the future could find the solution?

[furkizio]

There was formula in order date field on gsheet. I removed it and set order date from reference. [Order Id].[Order Date]
So it worked. Thank you again.

[Steve]

Do [Order Date], [Order Date], [Category], and [Marketplace] all have values in the form before you try to choose the campaign ID?