Conditional Formatting and Sum issue

95 views
Skip to first unread message

Drazen D. Babic

unread,
Jan 27, 2022, 10:40:46 PMJan 27
to Jam.py Users Mailing List
Hi guys,

I am trying to understand how to format expense/income scenario and provide correct Summary at the bottom of the view.
In ANY money transaction software, the input is a number. However, if the number is EXPENSE it is formatted with "minus" and sometimes colored in red.
If the number is INCOME it has no "minus" and sometimes colored in green.

However, the problem is a Sum.  Please see the screenshot from MS Access. How to do that? Please remember, it is always positive number we type in, but the formatting on the view is different, hence Sum must be correct.

ThanksAccountTransactions.PNG

Drazen Babic

unread,
Jan 27, 2022, 10:54:57 PMJan 27
to Jam.py Users Mailing List
Just to clarify, Test1 is the EXPENSE on the Form when selected as a Fee:

AccountTransactions2.PNG

--
You received this message because you are subscribed to a topic in the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jam-py/qDlZnWfLsrc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jam-py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/9e08d840-7df9-4729-afd7-8d6369989582n%40googlegroups.com.

Drazen D. Babic

unread,
Jan 28, 2022, 2:39:00 AMJan 28
to Jam.py Users Mailing List
Screenshot from 2022-01-28 15-15-30.png.


Ok, I'm getting there, this is the code in main Form. What I have a problem with is category. 

The Category is a Lookup List, so the format condition is: if Expense (2 in the lookup) make Actual Amount strong and negative (some color maybe, like red?). 
Also, I would love to JOIN a Category/Lookup List, like this Car/Income or Car/Expense, but I'm getting undefined.

 
function on_field_get_html(field) {
    if (field.field_name === 'actual_amount') {
        //if (field.field_name === 'category'  && field.value == 2) {
            return '<strong>' +  field.owner.transaction_amount.display_text + '</strong>';
        //}
    }
    if (field.field_name === 'category') {
        return field.owner.category.lookup_text + '/' + field.lookup_value.display_text;
    }

}

function on_field_changed(field) {
    var item = field.owner;
        if (field.field_name === 'transaction_amount') {
            item.actual_amount.value = item.transaction_amount.value;
        }    
}

What I'm trying  to do is the replicate MS Access Template Account Transactions , than I could make a movie for YT. But need to get there...


Thanks

Danijel Kaurin

unread,
Jan 28, 2022, 5:27:56 AMJan 28
to Jam.py Users Mailing List
Hi Dražen.

Here is code:

function on_field_get_html(field) {
    if (field.field_name === 'iznos') {
        if (field.value > 0) {
              return '<span style="color:  green  ;">' + field.display_text + '</span>';
        }
       
        if (field.value < 0) {
              return '<span style="color: red;">' + field.display_text + '</span>';
        }
    }  
}

Here is example in action:

example.PNG

Regard

Danijel Kaurin

Drazen D. Babic

unread,
Jan 28, 2022, 6:49:17 AMJan 28
to Jam.py Users Mailing List
Hi Danijel, 

thank you for your reply.

The Condition is not the value with the operator <> 0. In Accounting, they always type the positive value but the Condition is Expense or Income (you can see this on first MS Access pic). Or more general, Credit or Debit. 

Hence, the condition is a Lookup List with 1=Income or 2=Expense 

The problem is that the Lookup List is on a second table Category, which is a lookup to Description. The Description "Car" can be Expense, or it can be an Income (for example if a Car is driven for Uber :)

Hope this helps. This is real and common app for single entry accounting. It is even more complicated for Double entry accounting, which I tried here:

Still waiting for UUID to happen with Jam..
BR
D.

Drazen D. Babic

unread,
Jan 31, 2022, 1:32:37 AMJan 31
to Jam.py Users Mailing List
Hi guys 

this code:
    if (field.field_name === 'category') {
        return field.owner.category.lookup_text + '/' + field.lookup_value.display_text;
    }
will cause unexpected behaviour, pls see the video....No idea if this is a bug or my mistake. Removing the code fixes the App.



 
Screencast.mp4

Drazen D. Babic

unread,
Feb 8, 2022, 2:31:40 AMFeb 8
to Jam.py Users Mailing List
Hi everyone, 

I finally figured how to conditionally format the values. Please see the screenshot.
You can also see the HTML presentation of combined fields category/expense_or_income presented as green or red.

The only thing left is to submit the changes into the DB after the category is changed. Than the DB would have negative or positive value and the Total would be correct. The Total now is incorrect.

Screenshot from 2022-02-08 15-20-26.png

Here is the App if anyone wants to chip in:

Drazen D. Babic

unread,
Apr 16, 2022, 10:43:04 AMApr 16
to Jam.py Users Mailing List
Hi all, 

after quite some time, this is the new App version with needed functionality.
Here is the Export link:

This App is almost exact copy of MS Access AccountTransactions Template. As seen here:

Screenshot from Transactions.mp4.png
 
Screenshot from 2022-04-16 21-50-05.png

It also demonstrates how to use a function within the function. In this case it is:
function on_field_changed 

It "fires" another function called "calculate" when any of below two fields change:

function on_field_changed(field, lookup_item) {
    var item = field.owner;
    if (field.field_name === 'transaction_amount' || field.field_name
=== 'category') {
        calculate(item);
    }
}
The calculate function is this:

function calculate(item) {
    if (item.income_or_expense.value) {
        item.actual_amount.value = item.transaction_amount.value;
        if (item.income_or_expense.display_text === 'Expense') {
            item.actual_amount.value = -item.actual_amount.value;
        }
    }
    else {
        item.actual_amount.value = 0;
    }
}

The "Expense" is hard coded as it is in MS Access. The function changes the value to a negative value if we select Expense. It changes the same value to positive value if we select Income.
I think the same functionality in Access is done by the VBA or macro. 
If App is using some other language, we would need to use that instead of Expense.

To finish this App as per Access template, only a few reports are needed and maybe an Master-Details for Expenses/Income.

Take care.
Reply all
Reply to author
Forward
0 new messages