trying to compare 2 number ranges

[SAFETY_RAMCO]

Hi Guys!

Im having trouble figuring out a way to compare two separate number ranges, I'm stumped and do not know where to begin. 

I work for a farming company and we harvest strawberries. Strawberries are a row crop and each row is represented by a number 1,2,3 etc. 

we keep track of our harvest via a manual harvest log ex: 

date	rows harvested
2/1/23	1-150

Throughout the growing season certain materials are applied to strawberry rows to aid in production but once applied these rows cannot be harvested until a certain time period has elapsed. We track these rows in a similar manner as we do the harvested rows.. a basic table:

date	rows	re entry (harvest)interval
1/29/23	1-150	24 hours

In this case rows 1-150 were treated w/ a material which prevents those rows from being harvested for 24 hours which is why those rows were harvested on 2/1/23 well after the required 24 hour waiting period

Ok.. hope I havent lost ya'll ! I would like to create an app where treated rows are stored and upon the beginningn of a harvest date if rows that are to be harvested fall within the treated rows ... I get an alert. 

HOW CAN I ASK THE QUESTION: Do the rows that are set to be harvested today fall within the treated rows which cannot be harvested? if so= ERROR. 

[1minManager]

So yes this can be done.  But I suggest you give it some though as to how this app might get used in the future.  Since almost everyone starts with one idea and later on the apps doing 10X what they though it would.

With your idea you be able to do it with with these two bits of data in one table:

[Date Time] = 1/29/23 10:00
[Start Row] = 1
[End Row] = 150
[Type] = Treatment
[harvest interval hrs] = 24

 then enter

[Date Time] = 2/1/23 11:00
[Start Row] = 1
[End Row] = 150
[Type] = Harvest

 You'd then create a Valid_IF formula that runs on both the [Start Row] and [End Row] that checks for an earlier treatment <24hrs before.  But its a bit tedious  (and limiting) creating a forumla that checks for the 10-12 different ways the treament and harvesting records could overlap.  Since you might treat rows 1-150 but then add your harvesting 149-165.

My suggestion would be you'd start out with a table of Rows e.g.

Row 1
Row 2
Row 3
...

Then add a subtable to this called Events or whatever.  This is so you can individually record everything that happens to each rows.  So when you enter:

[Date Time] = 1/29/23 10:00
[Start Row] = 1
[End Row] = 150
[Type] = Treatment
[harvest interval hrs] = 24

What this would do is to use either an action or a bot to write a 150 records to the Events subtable.  1 Event for each Row.  Now your valid_if formula is a bit easier

COUNT(Select(Events[KeyColumn],AND(
   [Number].[Row]>=[_ThisRow].[Start Row],
   [Number].[Row]<=[_ThisRow].[End Row],
   [Type] = "Treatment",
   [Date Time]<TIMENOW()+"024:00:00"
)))=0

 Its also going to be much more function in the future.  

Hope this helps.

Simon, 1minManager.com

Appsheet developer for hire and reward 😉