Font Color changes when value changes

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
AEmpeno
Veteran Member
Posts: 50
Joined: 2018-01-04 18:48

Font Color changes when value changes

Post by AEmpeno » 2019-01-26 03:45

Hello,

I have fields named receiving.amountrecieved, batchesdetails.quantityused, receiving.balance.

What I would like to happen is the color of the font in receiving.balance will change to red when the value is less than or equal 5%.

Code: Select all

<?php
	$currDir = dirname(__FILE__) . '/..';
	include("$currDir/defaultLang.php");
	include("$currDir/language.php");
	include("$currDir/lib.php");
	
	/* grant access to all users who have access to the receiving table */
	$od_from = get_sql_from('receiving');
	if(!$od_from){
		header('HTTP/1.0 401 Unauthorized');
		exit;
	}
	
	$id = intval($_REQUEST['id']);
	if(!$id) exit;
	
	$total_quantityused = sqlValue("SELECT sum(QuantityUsed) from batchesdetails where ReceivingID='{$id}'");
	$AmountReceived = sqlValue("SELECT AmountReceived from receiving where ReceivingID='{$id}'");
	$Adjustment = sqlValue("SELECT sum(Qty) from adjustments2 where ReceivingID='{$id}'");
	
	$balance_supply = $AmountReceived - ($total_quantityused + $Adjustment);
	sql("update receiving set Balance='{$balance_supply}' where ReceivingID='{$id}'", $eo);
	
	$total_qty = sqlValue("SELECT sum(Qty) from adjustments2 where ReceivingID='{$id}'");
	
	$adjustment = $total_qty;
	sql("update receiving set Adjustment='{$adjustment}' where ReceivingID='{$id}'", $eo);

	echo number_format($balance_supply, 2);

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 525
Joined: 2018-04-01 10:12

Re: Font Color changes when value changes

Post by pböttcher » 2019-01-26 08:36

Hi,

can you explain a little bit in more detail where you want the font to change color. Is it in the tableview? Detailview? Is it when you enter your data in the detailview?
When are you running the php you posted to calculate the balance_supply?

AEmpeno
Veteran Member
Posts: 50
Joined: 2018-01-04 18:48

Re: Font Color changes when value changes

Post by AEmpeno » 2019-01-26 14:23

Yes I would like to see the font change color both in table view and detail view if possible. This will help me or any users be alerted of the balances in the inventory.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 219
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Font Color changes when value changes

Post by jsetzer » 2019-01-26 16:01

Hi all,

from my personal point of view and from my experience:
Instead of changing the font-color, I would insert an alert with an icon and textual hint inside like so:

Code: Select all

<div class="alert alert-danger" id="balance-alert">
    <i class="glyphicon glyphicon-exclamation-sign"></i> <b>Attention</b><br />Negative balance! ...
</div>

The reason for this:
8% of men (and a few % of women) have a color-blindness. Most of them have a red-green-blindness. This means, when you change the font-color, >8% of your users may not see any difference at all. They will see grey text or they will see grey text. Some of them may see gray text. :shock:

That's why I never rely on traffic-light-colors, only. I had to learn that lesson 15 years ago when I was asking my co-worker, why he did not acknowledge the red item in our software. He only asked me: "Which red item?". He did not see any red item.

If the balance-value is important for your process, from my point of view, you should give textual info additionally. Changing the font-color is possible, but possibly not the best way to reach your aim.

Best regards,
Jan

AEmpeno
Veteran Member
Posts: 50
Joined: 2018-01-04 18:48

Re: Font Color changes when value changes

Post by AEmpeno » 2019-01-26 17:37

Thanks for the info and the code, Jan!

I will try this option though. Will you guide me where should I add this code?

AEmpeno
Veteran Member
Posts: 50
Joined: 2018-01-04 18:48

Re: Font Color changes when value changes

Post by AEmpeno » 2019-02-01 01:39

Attached is a screenshot of my batches table in table view.

What I'm trying to do accomplish here is to change the font color or change the cell color if "SoldJars" become 5% left of "ProductionJars" So every time "SoldJars" dropped by less than or equal to 5% the color of the font color changes. This will help data encoder alert the management of stock status.

Appreciate any help or direction here. Thanks!
Attachments
highlight.png
highlight.png (144.23 KiB) Viewed 912 times

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 219
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Font Color changes when value changes

Post by jsetzer » 2019-02-01 08:24

Hi,

you can do this on client side with JQuery, or, which I prefer, you can use PHP, SQL and CSS:
  1. In your AppGini project, open Media-tab, [Configure...] the Link, set Behaviour to "No action".
  2. Follow this tutorial to change the display value: https://bigprof.com/appgini/tips-and-tu ... sing-hooks
Now it's time to figure out what SQL command is needed to get a coloured display of your values.

There are at least two alternatives:
  1. Let SQL return the desired colours depending on the value
  2. Let SQL return a specific CSS-class, and colouring (formatting) will be done using CSS.
I prefer the second option, because it gives me more possibilities just by changing CSS without changing PHP or SQL.

Have a look at the following SQL which I'm using in the first step just to figure out how to calculate the required css-class per value according to defined thresholds. There I have a nueric column named flaeche in a table named flurstuecke. You will have to use your own tablename and fieldnames.
chrome_2019-02-01_08-56-02.png
chrome_2019-02-01_08-56-02.png (5.18 KiB) Viewed 906 times

There I have defined thresholds of...
  • <100
  • <1000
  • >= 100
Depending on the value, the SQL statement will return something like this:
chrome_2019-02-01_08-59-28.png
chrome_2019-02-01_08-59-28.png (11.43 KiB) Viewed 906 times

Once again: The SQL statement above is just to check if my requirements will be met. Open your database workbench (I'm using Adminer or phpMyAdmin) and work on your SQL statement till you get the wanted result. You will have to change the conditions in the case when ... then ... else ... end-part of the SQL statement.

In the next step, from the SQL select statement we do not need the SELECT-part, the FROM-part and the LIMIT-part. Just take the CONCAT-part and put it in a $sql variable like this.

Code: Select all

$sql = "concat(
    '<span class="', 
    case 
        when flaeche < 100 then 'red' 
        when flaeche < 1000 then 'yellow' 
        else 'green' end , 
    '">', 
    flaeche, 
    '</span>'
)
";
Caution This will not work, yet, because we need to escape the double-quotes " by using a backslash-character. So inside the sql-statement, replace all "-characters by \".

Your code should look like this now:

Code: Select all


if ($field == '`your_tablename`.`your_fieldname`') {
    $sql = "YOUR SQL STATEMENT HERE";       
    $new_options[$sql] = $caption;
} else if ( ... ) {
    // ...
} else {
    /* Keep all other fields as-is */
    $new_options[$field] = $caption;
}
You will not see any changes, yet, unless you are using your browsers developer tools and inspect the value-elements in your table to verify that they are now placed inside a <span class="..."> tag.

Last step is to create your CSS styles, for example:

Code: Select all

.red { color: red; }
.yellow { /* ... */ }
.green { /* ... */ }

Using this technique, I am able to almost show everything I want. Sometimes it's a bit tricky with the quotes and double quotes between SQL and PHP. Have a look at these examples from one of my projects. I'm using this same technique to render the output in my table views:
chrome_2019-02-01_09-15-30.png
chrome_2019-02-01_09-15-30.png (5.48 KiB) Viewed 906 times
chrome_2019-02-01_09-17-46.png
chrome_2019-02-01_09-17-46.png (5.05 KiB) Viewed 906 times

Hope this method will help you in many cases!
Best regards,
Jan

PS: On my "showcase" page, https://www.bizzworxx.de/en/appgini-improvements/, there is a chapter named Table view improvements showing a couple of examples.
Last edited by jsetzer on 2019-02-01 08:30, edited 1 time in total.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 219
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Font Color changes when value changes

Post by jsetzer » 2019-02-01 08:27

AEmpeno wrote:
2019-01-26 17:37
I will try this option though. Will you guide me where should I add this code?
Have a look at the section tablename_dv() on this help page:
https://bigprof.com/appgini/help/advanc ... blename_dv

You can prepend or append your html to the $html variable in TABLENAME_dv() function.

Best regards,
Jan

AEmpeno
Veteran Member
Posts: 50
Joined: 2018-01-04 18:48

Re: Font Color changes when value changes

Post by AEmpeno » 2019-02-01 16:41

Thanks Jan! I appreciate all the help though.

So I was following your code and instruction,however, I'm still confused where should I insert these set of codes.

Also what do mean by this
Follow this tutorial to change the display value: https://bigprof.com/appgini/tips-and-tu ... sing-hooks

1.
<div class="alert alert-danger" id="balance-alert">
<i class="glyphicon glyphicon-exclamation-sign"></i> <b>Attention</b><br />Negative balance! ...</div>
2.
if ($field == '`your_tablename`.`your_fieldname`') {
$sql = "YOUR SQL STATEMENT HERE";
$new_options[$sql] = $caption; } else if ( ... ) {
// ...
} else {
/* Keep all other fields as-is */
$new_options[$field] = $caption;
}
3.
.red { color: red; }
.yellow { /* ... */ }
.green { /* ... */ }

Also what do mean by this
Follow this tutorial to change the display value: https://bigprof.com/appgini/tips-and-tu ... sing-hooks
I do like your suggestion, I'll prolly add another column "status" of each row.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 219
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Font Color changes when value changes

Post by jsetzer » 2019-02-02 12:33

Regarding your questions:

Did you read and work through that tutorial? In the tutorials there it is decribed step by step how you can use PHP code in hook-files and hook-functions to modify the values displayed in table view "on the fly" and how to add html-code to your details views (dv).

As written before, prepend or append the html code snippet for the negative-balance-alert to the $html variable, for example:

Code: Select all

$html = '<div>...</div>' . $html;
or

Code: Select all

$html .= '<div>...</div>';
This code has to be put into your TABLENAME_dv function in hooks\TABLENAME.php.


There are different ways to inclue your custom CSS in AppGini applications. One way is to edit hooks\header-extras.php and insert the following code:

Code: Select all

<style>
/* YOUR CSS DEFINITIONS HERE */
.red { color: red; }
.yellow { /* ... */ }
.green { /* ... */ }
</style>
Allthough AppGini is easy to use and produces working CRUD-applications:
If you need to change the standard-behaviour, you definately need to know PHP, some SQL and - especially for UI-changes - JQuery/Javascript and CSS. The guys and gals here in the forum will do their best to give you hints or point you into the right direction.

Best regards,
Jan

AEmpeno
Veteran Member
Posts: 50
Joined: 2018-01-04 18:48

Re: Font Color changes when value changes

Post by AEmpeno » 2019-02-03 04:19

Thanks again Jan!

AEmpeno
Veteran Member
Posts: 50
Joined: 2018-01-04 18:48

Re: Font Color changes when value changes

Post by AEmpeno » 2019-02-05 03:20

Hello,
Here is my take.I tested first my sql statement on my phpmyadmin and its working fine. I then inserted the whole statement to hooks/batches.php

Code: Select all

/* Font color changes */
$old_options = $options->QueryFieldsTV;
foreach($old_options as $field => $caption){
	if ($field == '`batches`.`SoldJars`') {
	$sql = \concat(
	'<span class=\', 
		case 
		when SoldJars < 100 then 'red' 
		else 'green' end , 
		'\>', 
		SoldJars, 
		'</span>'
		)
		";;       
		$new_options[$sql] = $caption;
		} else if ( ... ) {
		// ...
		} else {
		/* Keep all other fields as-is */
		$new_options[$field] = $caption;
		}
		return TRUE;

Code: Select all

function batches_dv($selectedID, $memberInfo, &$html, &$args){$html .= '<div>...</div>';}
This code is inserted in header-extras.php

Code: Select all

/* YOUR CSS DEFINITIONS HERE */
.red { color: red; }
.yellow { /* ... */ }
.green { /* ... */ }

Post Reply