Using _cfv with ktlRefVal in a grid

[amandajane-mo]

Hi Norm,

I want to compare 2 values, both in a grid view, and highlight one of the values if it's less than the other:

If actual quantity is less than required quantity highlight actual quantity.

I've tried using ktlRef value, with no success. I've tried many incarnations, here is one example (6499 is actual quantity, 6495 is required quantity)

_cfv=[field_6499, lt, ktlRefVal, ,red, ,], [ktlRefVal, field_6495]

I've run through the code and we end up here, with the refVal undefined:

 .catch(e => {
                                ktl.log.clog('purple', 'Failed waiting for selector in colorizeFieldByValue / getReferenceValue.', viewId, e);
                            });

Having re-read the _cfv Wiki it doesn't appear that comparing 2 values in a grid is supported, is this correct? Or am I missing something?

Thanks
Amanda

[cortexrd]

Amanda,

No this is not yet supported. But I'd really like to do it. I checked quickly and it's not going to be easy. I need to talk to Charles on how to implement this without breaking anything.

I'll keep you posted,
Norm

[jbk68]

Unless I missed something in Amanda post, it works for me. Maybe the extra "," in the first part is causing the issue for Amanda ???

Here is what I did:

_cfv=[field_541,gt,ktlRefVal,#f70707,,], [ktlRefVal, field_540,]
_cfv=[field_541,lt,ktlRefVal,#0bfd00,,], [ktlRefVal, field_540,]

field 540 = "99" field 541 turns red, field 540 = "101" field 541 changes to green. If they both equal "100" there both black.

I just can't get it to work for me if the fields have a currency values in them.

[cortexrd]

Amanda,

I found a solution!!! And it's only 2 lines of code. It shouldn't disrupt anything.
Let me know if it works by using the dev version.

@jbk68: you don't include the last comma after field_540. This tells the KTL to find a view without a title or ID and derails the process.

Norm

[cortexrd]

@jbk68
I just tested... it works with currencies as well.
ND

[jbk68]

If I remove the comma after field_540 it stops working. If I put the comma back, works perfectly!!! That is as long as the field doesn't contain currencies.

I haven't tried your solution in DEV mode yet. Getting ready to try it "without" comma after 540.

Thanks Norm...

[jbk68]

Hey Norm, I went into DEV mode, and I couldn't get any of the below to work including your solution to the currency issue. I tried removing the comma after field_540 and I couldn't get that to work. Just a fyi, those fields 540 & 541 are just TEST short text fields. I logged out of DEV mode, tried it without the comma behind 540, didn't work. Put the comma back behind 540, the test fields will change colors accordingly. Please let me know what else to try. I'm trying to finish this project and would really like the ability to have the colors change with currencies.

_cfv=[field_541,gt,ktlRefVal,#f70707,,], [ktlRefVal, field_540,] (short text fields)

_cfv=[field_541,lt,ktlRefVal,#0bfd00,,], [ktlRefVal, field_540,] (short text fields)

_cfv=[field_506,lt,ktlRefVal,#0bfd00,,], [ktlRefVal, field_472] (currency fields)

Thanks again Norm... JON

[cortexrd]

Jon,

Just by curiosity, have you tried with a number field instead of a short text (field_540)?

Norm

[cortexrd]

@jbk68

I think I've got another fix for non-numeric fields.
Try again the dev version please.

Norm

[jbk68]

When I put the knack app in dev mode, what version should I be seeing?

[cortexrd]

You should see: v0.21.2-dev

Follow the steps described here, to get in dev mode, but only on your device (without disturbing all other users): #186 (comment)

Norm

[jbk68]

I know how to switch to dev mode just wondering if when you switch it changed the version (
example 0.21.3 - dev).

Still can't get anything to work in dev mode. Text and numeric work in normal only if I leave the comma after field_540. Nothing I've triedworks with a currency field. ???

[cortexrd]

No, we're still at 0.21.2-dev

You're having what is called a typical "corner case".
I'd need access to your app in some way.

Can you create a debug page for me?
Or better: share your app?

Norm
P.S. Midnight here... bed time for me!!!

[amandajane-mo]

Hi Norm,

Tried in dev, works for me until the refVal is 0:

_cfv=[field_6499, lt, ktlRefVal, ,var(--warning), ,], [ktlRefVal, field_6495]
_cfv=[field_6499, equ, ktlRefVal, ,var(--success), ,], [ktlRefVal, field_6495]

Thanks Norm amazing work

Amanda

[cortexrd]

@amandajane-mo,

Can you give it another try now please? In dev.

Also, I've inverted the precedence order, which seems more logical now when you think about it: the fields' keywords are applied first, THEN the views' keywords.

This means that the fields are applied across the board by default, and if you need to further customize per view, you can do it.

I don't even understand why I did it backwards in the first place.

Does it make more sense to you also?
Does it affect your app?

Norm

[amandajane-mo]

Hi Norm,

It works! And makes perfect sense. No adverse effects on any other _cfv.

The issue now is that if there's no data in the field we're comparing (Actual Quantity in my case) then it gets highlighted in amber, in some use cases this maybe the desired behavior and in others not.

What do you think?

Amanda

[cortexrd]

Try the b option to ignore blanks.
ND

[amandajane-mo]

Yep, all good, I hadn't spotted that option.

Thanks

[jbk68]

Hey Amanda, yeah Norm is pretty amazing. Helped me up to midnight last night trying to figure out why I can't get it to work in dev mode.

Question for you, do I have to assign myself any special roles (ie developer) to be able to see changes in dev mode?

When I click current version in upper right corner, log in and turn on dev mode and see "0.21.2-dev" Is this all I need ??

Using KTL, I can get it to work perfectly in normal mode unless values include currencies, but nothing works in dev mode.

_cfv=[field_541,gt,ktlRefVal,#f70707,,], [ktlRefVal, field_540,] ** ONLY WORKS WITH COMMA AFTER field_540 ??? **

_cfv=[field_541,lt,ktlRefVal,#0bfd00,,], [ktlRefVal, field_540,] ** ONLY WORKS WITH COMMA AFTER field_540 ??? **

I finally got the currency to work but had to use a java script to make it work, sure would be a lot easier to use KTL (see below):

$(document).on('knack-view-render.view_262', function(event, view, data) {
setTimeout(function() {
$('#' + view.key + ' tbody tr').each(function() {
var field472Text = $(this).find('.field_472').text().trim();
var field506Text = $(this).find('.field_506').text().trim();

        var value472 = Number(field472Text.replace(/[^0-9.-]+/g, ""));
        var value506 = Number(field506Text.replace(/[^0-9.-]+/g, ""));

        if (!isNaN(value506) && !isNaN(value472)) {
            if (value472 > value506) {
                $(this).find('.field_472').attr('style', 'color: red !important; font-weight: 625;');
                $(this).find('.field_506').attr('style', 'color: #23b042 !important; font-weight: 625;');
            } else if (value506 > value472) {
                $(this).find('.field_506').attr('style', 'color: red !important; font-weight: 625;');
                $(this).find('.field_472').attr('style', 'color: #23b042 !important; font-weight: 625;');
            } else {
                $(this).find('.field_472, .field_506').attr('style', 'color: black !important; font-weight: 625;');
            }
        }
    });

[cortexrd]

Jon,

Your use case is somewhat special and can't be debugged by using only explanations and guessing. I really need to single-step in the code and examine variable's content, field types, field format, etc., to pinpoint the issue, all this in real-time, in the real application.

The only way to do this is for you to create a temporary debug page for me to log-in and see dummy data that replicates the issue. I only need one grid, one row and two fields. It takes about two minutes to do this.

Norm

[cortexrd]

Thanks Jon for giving me a debug page.

I’ve checked your _cfv keyword and as I expected, the fact that you have an extra comma at the end of [ktlRefVal, field_id,] tells the KTL to use the view “” (empty string). So it fails and causes the rest of the code to generate undefined results.
By pure coincidence, it matches partially what you were expecting! But it’s unrelated – really.

I’ve added some protection against empty parameters like this, so it shouldn’t happen again. And if it does, an error log will be generated in the console, and the code will exit to prevent false results.

I'd ask you to remove all those commas from the view please, so I can resume debugging.

Thanks,
Norm

[cortexrd]

@jbk68,

I managed to simulate the _cfv I needed locally.

So I think I've got a solution that works with all field types, including currencies.

Please try the dev version and let me know if all looks good.

Norm

[jbk68]

All extra commas have been removed from the end.

[cortexrd]

In dev mode (debug this device) ... does it work?

[jbk68]

Sweet... Using dev mode on the debug page I shared with you, the currency fields are now changing colors as expected.

Hoping you implement it soon. Thanks for helping me with this issue... JON

[cortexrd]

Do a Ctrl+F5 just in case.

[jbk68]

Norm, I just tried dev mode in one of my main apps and added a couple test currency fields to one of the tables and it works as expected in this app.

Thanks again and please let me know when you implement it for normal use... JON

[cortexrd]

Done! The fix has been released in v0.22.2

If you remove the dev mode, you should see the same fix be applied.
Mark as Answer please if it does the job.

Thanks for both of you for reporting this :)

Norm