An additional feature for Dynamic Filtering

[kanefranco]

Hey!

I wrote support on the Ragic forum some time ago, as I was unsure how the Dynamic filtering works when it comes to the Link & Load feature in Ragic.

See here:
Question regarding dynamic filters for link & load! - Ragic Community

I want to be able to limit what records actually get updated when I execute a Link & Load on a given sheet.

I know that I can use Dynamic Filtering to limit what records will appear when selecting a record, when using the “Select from other sheet” feature.

And I “now know” that this has no bearing on what records will be updated, like I said, when executing a Link & Load across a given sheet. As all records will be updated…

My suggestion for a new feature, is such that, you could possibly, for instance, modify the already present Dynamic Filtering feature, so that we select an additional checkbox, when setting up a Dynamic Filter, to not only limit what records are visible when using “Select from other sheet”, but so too, to also limit what records are being updated in a Link & Load to those that have been filtered, as per the Dynamic Filtering setting, as discussed!

Let me know what you think!

Love,

Kane

“It’s always better to be safe, than sorry!”

[chiayun]

Hi Kane,

Thanks for the feedback!

Do you mean that when there are link and load settings in the fields, the data should remain as the values at the time it’s been executed, instead of updating into the latest value from sourced sheet?

[kanefranco]

Hi Chiayun,

Actually no sorry, I was not referring to values having been updated from the source sheet, or having remained as is.

That feature is available in Link & Load settings, you have the option to “tick a box” to keep loaded values synced with source. I believe!

What I am wanting to do, here, is limit the number of records that are updated when either a Link and Load is manually executed, or, as you have mentioned the feature,… when a Link & Load is executed periodically using the “keep values linked with source” feature.

Note: At the moment all records are updated in a given sheet when a Link & Load is updated (unless it was updated using an action button, in which case, only the single record will be updated).

So, what it is that I am looking for, here, is a feature to limit the records that are updated when a link and load is executed. Instead of all records as it is now…

So, I am thinking here, that… at the moment there is a feature that can be modified to accommodate the capability that I looking for - Dynamic Filtering. With dynamic filtering when it has been setup, you can specify which records can be selected; in either a stand-alone “select from other sheet” field, or in “one” such field that is otherwise being used in a Link & Load setup. To do this, and setup a Dynamic Filtering, we also go to the Link and Load settings.

Now, as I am trying to explain, we could possibly modify the Dynamic Filtering feature so that the same records available in the “selected from other sheet” field, when a Dynamic Filtering has been included, are the ones that are updated when the Link & Load is executed. Obviously, this would only be optional, and so, you would be able to select or de-select this option.

Again, obviously Ragic may wish to include this capability in some other way, and or separate from the Dynamic Filtering feature altogether!

Actually unlikely, but it could in some cases, be the case, that what records one would like to be able to select, entirely differs, from what records one wants to be updated,

Anyway, I hope this explains what it is I am trying to do!

PS_ In my user case, I have a sheet of Property Listings and I would like to update only that which is currently “Listed”. Having it being updated via Link & Load, from source sheet MKTG Units, using “keep values synced with source” feature option. At this time, if I try to execute a Link & Load to Listings, all listings will be updated, both Listed and Unlisted, and this would be “bad” cause the unlisted Listings, no longer correspond with the MKTG Units, and so, should no longer be updated as well. Another way of looking at it, often times a MKTG Unit would have many past Listings, all having being generated using the data present in the MKTG Unit form. However, each time a new Listing is generated, the information being used, changes. So, updating any of the past Unlisted Listings from the source, would never be something that needs to be done. In fact, nor should it ever be done!

[chiayun]

Hi Kane,

Well noted. Thanks a lot for your patience explaining all the details. I will talk to our developers and keep you updated!

[kanefranco]

Hi Chia,

I think I may try using the Lock Records feature Locked Records (ragic.com) together with Batch Execute (ragic.com), to get the result that I looking for. As, as stated above, I would like to be able to limit the records that are being updated during my link and load synchronizations. I was wondering if, a new feature that would filter the records, would be the way to go. However, I am now of the opinion that the existing feature i.e. to be able to lock records within Ragic, will certainly facilitate what it is that I am trying to do!

Thanks for your assistance, problem solved now.

[funda.jeremy]

Hi Kane,

I don’t think Locked Record will prevent Batch Execution to Link and Load new value.
At in my test it still update the record through link and load JS function of:
entry.loadAllLinkAndLoad();

Or maybe you are talking about other function.

[kanefranco]

Exactly right Jeremy, I just tried Link & Load using an action button, on a locked record, and it execute. Despite having been locked.

I was hoping that somewhen would clear this up for me, if that in facts was the case! Thanks!

However… I don’t think that we can Link & Load an action button using the Batch Execute feature. Would be good through, as it would, also allow, basically what I am looking to do here, in the first place…

That is, I would like to limit the records being updated when executing a Link & Load on a specific sheet.

I was thinking and did initially suggest “above” that we could include this capability on the settings page for each Link & Load.

It could easily appear just below the Dynamic Filter feature, already present, and work similarly, however instead of filtering the entries for selection, it would instead limit what entries get updated when a specific Link & Load has been executed.

This feature/capability would allow for me to update only current property listings in my database, when using the “keep values updated with source” feature already present in Ragic.

[funda.jeremy]

If you only need to do the operation as an SYSAdmin.
You can still try using Manual Link and Load + Fixed Filter.
It will give you an option to update it only on entries that meet the Fixed Filter condition.
You can create a multiple version on the same form with Fixed Filter specifically for this purpose.

However, if you need to give other users this function.
In the mean time, you will need to use Custom JS Workflow to do so without Ragic built-in support.

[kanefranco]

Hi Jeremy,

Thank you for responding! I’m very interested if it’s that you have a solution, for me, as to how to limit what records get updated in a link and load!

You have proposed that I use a fixed filter on the sheet, and then any Link and Load will only execute on the records shown on the fixed filter view.

Actually, sorry, you said I should0in fact get the option when updating the Link and Load manually, to select all or just the filtered entries, i.e. like you said, when I am using a fixed filtered sheet only!

First things first Jeremy (and by the way thanks again for responding), is there any documentation from Ragic that describes this? Do you know?

As that would be most helpful!

I did try fix filtering my sheet, but I did not want to test executing the Link and Load though, in fear that it might update all my records.

I did not see the option to select all records or just filtered records either. I assume maybe it may be presented only after I select the “update valued with souse sheet” option.

So don’t feel like testing that out at the moment, as it stands, but will try to do so, when I get a chance.

Thanks again!

[funda.jeremy]

Sorry Kane,
I actually got confused with the function to recalculate formula, where you can choose to apply only to record filtereed by fixed filter.

I guess you can still use JS Workflow to do this.

Here is a universal workflow that you can put in the Global Workflow and call upon in your form.
Just setup a Action button with JS Workflow with something like this:
conditionalLinkAndLoad("forms1/1", {id}, 1000101, "Yes")
And it will only execute the Link And Load All for 1000101 that equal “Yes”.

For example, I want to update the Tel for Customer only on entries that has Update? = “Yes”

I ran batch execution, but only the 2 entries has Update? = Yes are updated.

Sample code:

function conditionalLinkAndLoad(formId, recordId, conditionFieldId, conditionValue) {
    //log.setToConsole(true); // Turn on the debug console for debugging
    log.println('Function conditionalLinkAndLoad started with formId: ' + formId);

    // Ensure formId starts with a leading slash if not already present
    if (formId.charAt(0) !== '/') {
        formId = '/' + formId;
    }
    
    // Query the form
    var query = db.getAPIQuery(formId);
    var entry = query.getAPIEntry(recordId);

    // Get the value of the condition field
    var fieldValue = entry.getFieldValue(conditionFieldId);
    log.println('Field Value: ' + fieldValue);

    // Check if the field value matches the condition value
    if (fieldValue === conditionValue) {
        log.println('Field value matches condition value. Updating link and load fields.');

        // Load all link and load fields
        entry.loadAllLinkAndLoad();

        // Save the updated entry
        entry.save();
        response.setMessage("Link and load fields updated successfully.");
    } else {
        log.println('Field value does not match condition value. No update needed.');
        response.setMessage("No update needed.");
    }

    log.println('Function conditionalLinkAndLoad completed');
}

[kanefranco]

Hi Jeremy,

Thanks for the code, that will be a great help!

I will leave this topic open, as Ragic may still want to implement this feature, in some way in the future. i.e. for those that prefer not to venture into “a world of code”.

Actually, I also still have one question. You mentioned that Link & Load still works on the Locked Records. I would like to confirm this, if I can, and maybe test it for myself, when I get a chance. I will create a new topic of this though!

Thanks again!

All the best!
Kane