Conditional Formatting – Feature Proposal

[Manuel Drews]

Hi there!

I’am using Tapforms for nearly 2 years now and i think
it is a very good product. As a Psychotherapist it helps
me to build a Database that is usable for my everyday
Counselling Work with my Clients.

Unfortunately there is exact one Feature missing which
would be very helpful. It is similiar to a Functionality found
in Excel and i would like to propose that feature to
the programmer of Tapforms.

It would be great if Tapforms allows Conditional Formatting.
I have several use Cases for this, i.e. one could be that
some Entries in the Database could be colored red, green or
blue depending upon states within the Database.

For example: If i have a Client that has only 3 Sessions
with me left it would be great to color code him Red.
Or – if i have a Client that has its first Sessions
with me (up to 5) to Color Code him Red.

This feature could work similiar to the Intelligent Filter
System but with the enhanced usability of using Colors.

Any chance for that?

Many greetings from Germany
and keep up your great work!

Manuel

[Brendan]

Hi Manuel,

Thanks for the feature request!

Right now the only conditional formatting available is on Number and Calculation fields which will display the result in red if the value in the field is negative.

But what exactly do you want to see coloured? You mention the Client should e coloured red or green or whatever. But what does that mean? The field label? The background colour for the record’s row in the list view? The Client’s first name and/or last name field?

I am working on adding the ability for customers to program scripts into Tap Forms to do various things. So far it’s just about calculating results or populating and updating the database, but I could see it being extended to allow for adjusting properties of the objects displayed themselves based on some logic and values from other fields in the record(s).

Thanks!

Brendan

[Manuel Drews]

Hi Brendan!

For clarification i just attached a photo to illustrate what i mean.
And besides that – sorry for my bad English, i learnt
it in school but it could be better, for sure.

It would be good to filter database Entries like
seen in the Column (Klienten) by Color. My Feature proposal
would be that the colors can be assigned by customizable
rules like: If Client Number 1 has reached 7 Sessions
color/visualize the database entry in red. Or more
boolean like: look up in Database the Session Entry.
If Session > 6 color the Client red.

By these rules one could sort Database entries by color.
For me it would be great to see with a blink of an Eye
which Client reaches his Session Maximum.

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi Manuel,

Thanks for the clarifications.

Your English is perfect. I didn’t even realize it wasn’t your native language :)

Much better than my French, which is the only other language I even know a few sentences in. :)

So you mean set the background colour of the record depending on some values in the database.

It may be possible to do that with my new scripting engine. But it might not make it into the first release of that. I’m focusing more right now on manipulating the data rather than affecting the look of the output.

Thanks!

Brendan

[Manuel Drews]

Well, thats good to hear!

Yes exactly, i imagine something like a little rules builder with
boolean operators which can change the appearance of entries (colors)
depending on Values.

I’am very excited about your scripting engine and the prospect
that the feature may be coming after a few releases.

My compliments for your great work – Tapforms is by far
the best and reliable Database Builder on the Mac!

Many greetings from Germany,

Manuel

[Brendan]

Thanks for the kind words Manuel! Very much appreciated. :)

[Ryan Tucker]

I would love something like this as well.

Just like in numbers or excel, if cell is equal to “x” then set multi column list cell color to “Red”.

This would allow users to highlight “draw attention” specific data. Right now user can’t even set a cell color manually, only the entire row color.

[Paul Lees]

Hi,

I use the forms, amongst other things, to track currency of data by date. It would be useful to have a cell which has, say 3 or months =to run before expiry to be coloured green, one which has less than 3 months to expiry to be amber and those which have passed their expiry date values to be rated. So if something expired on, say 30th September 2021 the cell would contain the date 30/09/2021 (sorry UK style dates). Prior to 30th June it would be coloured green. From 1st July the cell would be coloured amber right up until 30th September. On 1st October the cell would be coloured red until a new date, some time in the future is entered into the cell and it reverts to green.

Thanks

[Sam Moffatt]

You can set the colour of a record via scripting (setRecordColor) and you could do that via a script field that checks your date versus the current and updates the record colour and also that would be a field you could search on (Good, Warn, Expired). One other limitation is that Tap Forms won’t re-evaluate all records in a form without being asked to do so which for these date based use cases you will need to click on the “refresh records list” to update the calculations for all of your records. There was a post on programmatically setting record colour that might be helpful for you as well.

[Paul Lees]

Thank you Sam Moffatt,

What yon suggest seems to be a good solution. So, the million dollar question, how do I actually setRecordColor for a script cell and how do I make it compare cell content to the current date? I am very new to Tap Forms and not really much of a techy – so need simple “do this, then do that” type instructions please…

Cheers

[Sam Moffatt]

I did a quick video and posted on YouTube walking through create a new form with a couple of fields, creating a new script field that can be used to set the record colour and then I walk through using a calculation field to make the script a little simpler.

Here’s the final script from the video to make it easier to copy that portion in (don’t forget to change the record.getFieldValue lines to your own field IDs):

function Record_Colour_Setter() {
	var rating_date = record.getFieldValue('fld-072414fe5ab04e93aba36c1051dc1f22');
	if (!rating_date) {
		record.setRecordColor(null);
		form.saveAllChanges();
		return;
	}
	
	var date_difference = record.getFieldValue('fld-14c4599832f74205a842c2c4d666709b');
	
	if (date_difference <= 0) {
		record.setRecordColor("#FF0000");
		form.saveAllChanges();
	} else if (date_difference >= 90) {
		record.setRecordColor("#00FF00");
		form.saveAllChanges();
	} else {
		record.setRecordColor("#FFBF00");
		form.saveAllChanges();
	}
}

Record_Colour_Setter();

[Brendan]

One other workaround without having individual cell colours, if this is for seeing different statuses, you could use a Pick List with different colours for the different record statuses. Also if you set the “Record Colour Field” setting on the form to your field that has the Pick List assigned to it, Tap Forms will put a colour marker to the left of the record’s row. This shows up on single or multi-column list view. It would help you to get a quick visual of the status of records by colour. This is different than setting the entire colour of the record.

[Paul Lees]

Thank you to both Sam and Brendan.
It all sounds a bit beyond me at the moment but that is good as it gives me a challenge – I will work on it and hopefully get it right.
Again, many thanks for your patience and help
Paul

[Sam Moffatt]

With a bit of practice I think you can get there. Scripting in Tap Forms unlocks a large amount of power to add custom workflows and interactions, so if you’re able to get the knack of it you can do a lot more.

I did a follow up video on the pick list idea as well as including some other suggestions from Brendan as well. The pick list approach is a little more nuanced and I must admit it’s a great suggestion because I use pick list based record colouring in a few of my own forms.

[Paul Lees]

?????

[Paul Lees]

Hi Sam,

Im afraid most of what you did in the video was way way beyond me. I did set up a trial database and tried to copy exactly what you did but failed miserably, despite devoting some 2 hours to it against your 16 minute video!! (Guess that’s why I’m a hell pilot and not a programmer.) I kept getting a scrip error on line two when running the check on the field script and not knowing diddly couldn’t rectify it.
I’m not sure that I will crack this one to be honest but will try again soon.
Once again thanks for trying for me.
Cheers

Paul

[Paul Lees]

Sam,

Rather than blocking the forum with my problems, can I contact you directly to demonstrate exactly what I want please?
Cheers

Paul

[Sam Moffatt]

You’re welcome to shoot me an email but I also don’t mind having the conversation on the forum. The advantage of the forum is that multiple folk can respond and it may also help the next person who has similar questions as well. That’s part of the reason I started on the YouTube videos to try to show off visually, as well as textually, potential solutions to problems. If you want, perhaps start a new thread in the script talk or otherwise my username is my email, pretty easy to find.

[Author]

Posts