I have two Tables, Freezers, which contains the Freezer Master Data, and Temp Check which contains the Temperature check records.
I want to allow users to enter temperature records for valid freezers(ones in the master data table), but not unless at least 30mins have past since the last temp check. This is to ensure that a user doesnโt keep entering the temp check over and over again.
I have the basic one for ensuring the freezer enters a valid master data freezer, but having issues getting the time restricted part.
so far I have been trying the following in the VALIDIF -
NOT(IN([_THIS], SELECT(Temp Check[Freezer ID], [Checked Time] < TIMENOW()-โ30:00โ))),
but not getting very farโฆ
Any suggestions would be highly appreciatedโฆ
Please try below.
Please have [Checked Time] as datetime type column instead of just a time type column. This will ensure any rollover from one date to another date if it so happens will not produce incorrect results.
Then the valid_if of the [Checked Time] column can be
MAX(SELECT(Temp Check[Checked Time], [Freezer ID]=[_THISROW].[Freezer ID])) < [Checked Time]-"000:30:00"
Many thanks for your response, Suvruttโฆunfortunately, it still allows the same freezer to be input immediately afterwards, so doesnโt workโฆinteresting formula though, but still back to the drawing boardโฆ
have the DateTime column already in the table so it was an easy thing to switch the formula and try itโฆ
Thank you for the update. As I normally do for such bit intricate solution suggestions, I may update you that I tested it before suggesting and it perfectly works for me. I again checked it just now before responding to you in this note as well.
One thing you may wish to note though. In my testing the child record ( Temp Check table) is saved. So that recordโs reference is available for cheking valid_if condition for next [Temp Checked] data entry.
As shown in the below image ,if I try to add a child record within 2 minutes of the previous entry, the valid_if of DateTimeEntry column does not allow reentry of new record. in this case, I am adding an โorder Detailsโ child recod to an โOrdersโ table parent record.
I can share a video also of the date entry if so required.
Ahh, now i understandโฆyou are putting the ValidIF in the checked Date column, I have been using it in the Freezer ID column instead to restrict entry for freezers that have already been checked within the 30min periodโฆ
Interesting approach, I could use this method by adding a validation Column that checks the date and only allows saving if the freezer has not been checked in the past 30mins.
I had hoped to get the error the moment the freezer ID is scanned, thus allowing the user to reenter another freezer that has not been checked. (The freezer IDs are scanned via QR codes)โฆ
Got it at Last!
Didnโt use your formula, but used another method based upon your solution above, many thanks!
I created a virtual column called vValidFreezerEntry, and tested the Checked DateTime there with the below formula -
IF(
LOOKUP(MAXROW(โTemp Checkโ,โChecked DateTimeโ,[Freezer ID]=[_THISROW].[Freezer ID]),โTemp Checkโ,โInspection IDโ,โChecked DateTimeโ)
<[Checked DateTime]-โ000:30:00โ,
โValidFreezerEntryโ,
โNotValidFreezerEntryโ)
then the ValidIF in the Freezer ID column was set to -
AND(IN([_THIS], Freezers[Freezer ID]), [vValidFreezerEntry]=โValidFreezerEntryโ)
All works as it shouldโฆI will look at seeing if I can do away with the Virtual field later and incorporate the entire two steps in one ValidIF to make it more compact and elegant, but this works for nowโฆ!
Many thanks for your help and for setting me on the right path!
keep scmilinโ!
Mark Wilhelm
Thank you for posting the update and nice to know you solved it per your requirement. All the very best with your app creation.
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |