Validating Data range

[guscuesta]

Hi, Im new to the community, Ive made some apps with Appsheet, but I cant find the way for validating a date range. Havent worked with dates in past apps.

This is what i want to do.

I have a two tables (MasterData, and Weeks). In the Weeks table I have 3 columns the number of the week (WeekNum) based on business information. The start date of the week and the end of the week.

What I need is that when some one inserts a date on a form from the MasterData table, apsheet returns the week number that matches that range.

This is the expressión Ive been trying to create in my MasterData base but it gives me the List of all values in WeekNum, instead of the number that matches the date range.

select(Weeks[WeekNum],
AND(
Any(Weeks[Start])<=[_ThisRow].[Date]
,Any(Weeks[End]>=[_ThisRow].[Date]
))

Thanks

[Aurelien · Accepted solution]

Hi @guscuesta

I think you are using SELECT improperly.

What about that ?

ANY(
  SELECT(
    Weeks[WeekNum],
    AND(
      [Start]<=[_ThisRow].[Date],
      [End]>=[_ThisRow].[Date]
    )
  )
)

Also, you can have this formula recently released by AppSheet Dev Team:

help.appsheet.com

ISOWEEKNUM()

ISO week number of the year from Date or DateTime

For reference:

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[guscuesta]

Excelent that solved my problem, as you say I was not using SELECT right.

Thanks