You are viewing limited content. For full access, please sign in.

Question

Question

show result count from lookup table query in a business process form

Forms Rules Laserfiche Cloud Forms Layout Designer How To
Updated January 7
asked on January 6

I have created a form that uses a lookup table query, I want to be able show the number of rows found on the form before the next step to allow the user to refine their search if they get more results than expected.

Although all the Laserfiche AI searches I have done indicate it can be done using a token called "result count" it seems this is not possible without invoking a workflow and running the rule from there. I am trying to do this from a message start event where choices are made about the type of search required.

Thank you for you consideration of this perplexing problem.

Peter

0 0

Answers

APPROVED ANSWER
replied on January 7 • Show version history

If you want to show how many results there are from the item selected in the single line you should have two rules:

Rule 1: Find Entries by Author = select X columns (whatever you want to be selectable in that single line) where author = Y

Rule 2: Find Entry Result Count by Author and X = select count of _key where author = Y and "whatever you want selectable from rule 1" = Z

Rule 1 fills the single line, and rule 2 fills the results when something is selected in the single line populated by rule 1

 

If you want to see how many results are populated by the lookup populating the single line you'll want these two rules:

Rule 1: Find Entries by Author = select X columns (whatever you want to be selectable in that single line) where author = Y

Rule 2: Find Entry Result Count by Author = select count of _key where author = Y

Rule 2 should populate just a single value regardless of whats chosen from the single line.

 

Pro tip: if you want to do both, just make three rules Rule 1.1, Rule 1.2, and Rule 2.2!

0 0
SELECTED ANSWER
replied on January 7

Hah, got it, . missed changing the function to count.

Works now, thank you so much for your help.

As per the AI search and LLForm objects, I was suspicious of the code as it looked like classic coding. That said, what would I know ;)

Now I noted recently someone on one of the Aspire programs said, no need to use classic designer any more as the new designers does the majority of things necessary.  Your saying this is a case of not incorporated in the new designer ?

Thanks again, hope I might meet you at Empower

0 0

Replies

replied on January 6 • Show version history

AI was mostly correct, as you noticed result count is a workflow only token. I'd recommend adding another column to your query that is "count of _key" so you have it available as a column to fill your form with. (edit: count requires a groupby so i'd suggest using a separate query with the where clause using the search parameters and only returning the count column)

Another option is to use a field underneath the table I'm assuming you're using to load lookup results to and use the COUNT formula in that field. Its a little redundant as the table does show how many rows it has already, but this would allow you to do more logic with field rules or blocking submission until the count is less than a specific number.

 

0 0
View 6 previous replies
replied on January 6

Thanks Zachary, I was just watching your video Advanced JavaScript in the Form Designer. Is it possible to get the example code/forms your used so I can have a closer look.

Regarding this issue, I found this suggestion on the AI by rephrasing the question see suggested code below. Funny thing is I can not get the ruleId code using the LF-AI recommended jsonForm command in developer console. it generates a "

VM891:1Uncaught ReferenceError: js is not defined at <anonymous>:1:1" error

Your suggestion "use a second query" seems simpler.

For background I have a table with authors and titles, and entries that I use to generate list of potential documents to review. I just want to be able to show on the form page the number of hits. I am not generating a table from the results, I have thought about that, but I am still trying to get a grip on the basics of Forms while admittedly trying to do more complex stuff, eh is it not all ways so ?

I will look into the second query ID not 100% sure how to do that but will give it a try. Any thoughts on the ruleId appreciated.

Thanks, Peter

Code from AI:

 $(document).on('lookupcomplete', function(event) {
       // Check if the event corresponds to your specific lookup rule
       if (event.ruleId == 'yourRuleId') {
           // Get the number of rows returned
           var rowCount = event.results.length; // Assuming results is an array of returned rows
           // Display the row count in a specific field or alert
           $('#rowCountField').val(rowCount); // Update a field with the row count
           // Alternatively, you can use an alert
           alert('Number of rows found: ' + rowCount);
       }
   });

0 0
replied on January 6

I definitely recommend the second lookup approach. For using a second query, the select should look like the following image. The where should just match what your other query is doing so you get identical results back


For JS make sure you are asking AI to use the LFForm object only because that code only works for classic forms. Regardless, the lookup event does not give you the resulting rows object and the code shows looking at a form field so implies the form was filled with a count.


Regardless, the lookup event and other methods are documented here, you can find the rule id when you configure the lookup rule

0 0
replied on January 7

Thanks Zachary, investigating

0 0
replied on January 7

I think I am missing something. When I preview or run the form I get a list of results 9 numbers which do not seem to correlate to entry number or number of entries.

0 0
APPROVED ANSWER
replied on January 7 • Show version history

If you want to show how many results there are from the item selected in the single line you should have two rules:

Rule 1: Find Entries by Author = select X columns (whatever you want to be selectable in that single line) where author = Y

Rule 2: Find Entry Result Count by Author and X = select count of _key where author = Y and "whatever you want selectable from rule 1" = Z

Rule 1 fills the single line, and rule 2 fills the results when something is selected in the single line populated by rule 1

 

If you want to see how many results are populated by the lookup populating the single line you'll want these two rules:

Rule 1: Find Entries by Author = select X columns (whatever you want to be selectable in that single line) where author = Y

Rule 2: Find Entry Result Count by Author = select count of _key where author = Y

Rule 2 should populate just a single value regardless of whats chosen from the single line.

 

Pro tip: if you want to do both, just make three rules Rule 1.1, Rule 1.2, and Rule 2.2!

0 0
SELECTED ANSWER
replied on January 7

Hah, got it, . missed changing the function to count.

Works now, thank you so much for your help.

As per the AI search and LLForm objects, I was suspicious of the code as it looked like classic coding. That said, what would I know ;)

Now I noted recently someone on one of the Aspire programs said, no need to use classic designer any more as the new designers does the majority of things necessary.  Your saying this is a case of not incorporated in the new designer ?

Thanks again, hope I might meet you at Empower

0 0
replied on January 7

Ya I'm working with the team to make the AI smarter about code suggestions since most people are using the new designer. Should have that ready by empower this year.

For that note from Aspire, this is one of those cases that kind of falls out of the realm of forms and into that of lookups in process automation. Neither classic or new supports result count. That comment is typically made when comparing customizations done using JavaScript in classic that you can use out of the box settings for now.

Hope to see you in my advanced js course at empower!

0 0
replied on January 7

I have one more question that perhaps might be better to create a new thread ?

The same form I have a custom html field with the following code.

<iframe src="https://app.laserfiche.ca/laserfiche/DocView.aspx?repo=r-0001xxxxxx0&amp;customerId=1xxxxxxx7&amp;id=17726#?openmode=PDF&amp;lang=en-US" height="500" width="800" title="Iframe Example"></iframe>

This accesses a document on our server entry # 17726

I want to exchange the "src" to a string variable to dynamically change the entry number with a variable. ie 17726 to 12345

I already have a variable that I pull from a table just having difficulty passing the variable to the iframe. The field name is EntryID and the variable is called  V___Entry_ID_

0 0
replied on January 7
0 0
replied on January 6

I definitely recommend the second lookup approach. For using a second query, the select should look like the following image. The where should just match what your other query is doing so you get identical results back


For JS make sure you are asking AI to use the LFForm object only because that code only works for classic forms. Regardless, the lookup event does not give you the resulting rows object and the code shows looking at a form field so implies the form was filled with a count.


Regardless, the lookup event and other methods are documented here, you can find the rule id when you configure the lookup rule

You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...