'blank' date, change to something else..

AppSheet AppSheet Q&A
1

Hi

I have been making a app on and off for the last couple of weeks to use for tacking field service jobs etc for our engineers. I have made pretty good progress etc but have one problem and I am not sure where I would need to look to fix it..

stomuk_0-1663857627261.png
stomuk_0-1663857627261.png429×483 30.8 KB

I have one of the views set as deck and to group jobs by [attend date]. This all works well.. however, sometimes when a job is added we will not add a [attend date] as it won’t be decided until another time. This means that the field is empty which means I get the blank word appearing instead. Is there a way of changing this blank word to something more appropriate? I know I could put a default date in for when none is entered but this wouldn’t really be a solution either.

If anyone has any ideas or suggestions it would be appreciated :slightly_smiling_face:

2

I think the best approach is to add another column defined as Text used strictly for grouping. When there is a date in the [Attend Date] field then insert the date as text into the new column. When NO date has been assigned then insert some identifying keyword - “Unassigned” for example. Then change the grouping in the view to use the new column.

3 Likes
3

Thank you for the reply. I’ll have a experiment and see what I can do. I didn’t think of trying it that way at all. Hopefully using virtual columns for it will work. something along the lines of ifblank then use “no date assigned” otherwise use stored date from the [attend date] column…

Hmm.. thank you, think that’ll work :grinning_face_with_smiling_eyes:

Just tested, works! :grinning_face_with_smiling_eyes:

stomuk_0-1663860660622.png
stomuk_0-1663860660622.png378×509 35.6 KB

Used the following in my code for the VC:

CONCATENATE(
IF([Attend Date] = NOTBLANK,CONCATENATE(

INDEX(LIST("", "Mon", "Tues", "Wed", "Thu", "Fri", ""), WEEKDAY([Attend Date]))

,TEXT([ATTEND DATE],“ dd",)
DAY([attend date]),
 
IF(
	IN(
			DAY([attend date]),
			LIST(11, 12, 13)
		),
		"th",
	SWITCH(
			RIGHT(DAY([attend date]), 1),
			"1", "st",
			"2", "nd",
			"3", "rd",
			"th"
		)
	)

,TEXT([ATTEND DATE],“ mmm - ",)
,
),"! No Date Assigned !")
,
)

Probably not the cleanest ‘code’ at the moment but it works for what I need. The only slight snag is that it lists numbers before letters in the listing so I had to add a ! onto the ‘! No Date Assigned !’ so it lists before the other dates so its clear they need assigning still.

Works well, thank you for the suggestion :slightly_smiling_face:

3 Likes