Conditional Formatting and Sum issue

[Drazen D. Babic]

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.

Thanks

[Drazen Babic]

Just to clarify, Test1 is the EXPENSE on the Form when selected as a Fee:

--
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]

.

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...

Here is Export: https://drive.google.com/file/d/1dBgaEvru41snc0eaWCzvgvc7Ry5_-39W/view?usp=sharing

Thanks

[Danijel Kaurin]

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:

Regard

Danijel Kaurin

[Drazen D. Babic]

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:

https://groups.google.com/g/jam-py/c/1rejdrNhaQE/m/-sa6khcWBgAJ

Still waiting for UUID to happen with Jam..

BR

D.

[Drazen D. Babic]

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.

 

[Drazen D. Babic]

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.

Here is the App if anyone wants to chip in:

https://drive.google.com/file/d/1FkvvDm4D-J1xWyuhaLRGBBWRlskJS3Ca/view?usp=sharing

[Drazen D. Babic]

Hi all, 

after quite some time, this is the new App version with needed functionality.

Here is the Export link:

https://drive.google.com/file/d/1FgHqafOZTrsACcmiB6uuhW4kygPn8mi5/view?usp=sharing

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

 

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.