Lookup Field in DataView

How do I?
1

I use DataViews extensively in my forms. In the Generate Emails form, there is a page with a dataview to select members. entries look like:

Adams, John
Blow, Joe
Mittelman, Ron
...

Is it possible to have a search bar at the top of the DataView where I could type “smith” and have the dataview filtered for that name? This would work similar to any form with records, where you can type some text in the search bar above the records list on the left, and the records would be filtered to that name.

I found something about adding screen fields to the dataview, and it looked like it would work, but it is quite complex and just adding the screen field caused my data view to not display at all, instead a message saying it could not render the data view showed up. Then I removed the screen field and it worked again.

Perhaps I didn’t properly add the screen field. but the documentation said I needed to add functions and action buttons, and that it required using one or more of the underlying query’s parameters.

My DataViews only use UserKey as the parameter.

How can I have a search bar at the top of the DataView that works like the search bar above a form’s record list?

Alternatively, is there a way to scroll the data view until a specific record is showing instead of filtering the data view?

Thanks…

2

Hi Ron,

Thank you for bringing this question.

Have you seen these steps: Filter Customer | Five | Low-Code For Real Developers?

I think this application has an example you are looking for.

Please let me know if you have any questions.

Regards,
Elton S

3

Thanks for the suggestion Elton.
This application seems to mirror the article I read about filtering data views. But it doesn’t seem to use a DataView. There is one defined, but it is not used anywhere, according to its references. The form itself is a standard form and doesn’t seem to use a DataView.

M DataView, dvSelectedMembers, uses the dvqSelectedMembers query as its data source. That query only has UserKey as a parameter, since it is displaying records in the SelectedMembers table for that particular UserKey. the fields are UserKey, MemberKey, SortName and IsSelected.

Like the article, I added a screen field for the Dataview, called SortName. Then I changed the query to add the SortName parameter, with a value of {{five.field.SortName}}. In the query itself, I changed the Where to

WHERE `UserKey` = ?
AND SortName LIKE CONCAT('%', ?, '%')

similar to the example in the documentation.

Now, when I run the application and open the GenerateEmails form and navigate to the SelectedMembers tab, I get this:

image
image836×108 7.36 KB

If I open the query in design mode, and modify the Where as follows,

WHERE `UserKey` = '10000000-0000-0000-0000-000000000001'
AND SortName LIKE CONCAT('%', ?, '%')

then the query itself works fine, and returns records. So I presume that saying SortName LIKE “%%” (which it would be if the parameter is not supplied) doesn’t limit the query results at all.

So can you explain why the query can run, but the form itself with the embedded DataView will not even render?

I hope I have explained my situration properly.

UPDATE:
If I remove the screen field from the DataView entirely, everything runs normally. The query is restored to only one parameter, UserKey. Then if I add back a unique-named screen field, don’t give it a default value, then save and run, the above error comes back. I should be able to have a blank unused screen field in the DataView, right? But the moment I run and click on the SelectMembers tab, the above error returns.

Why would the DataView designer allow me to add a screen field but crash when I try to click on the form page holding that dataView?

4

Hi Ron,

Thank you for bringing this scenario to our attention.

Please use this link instead if you haven’t seen it already: Work With Data Views | Five | Low-Code For Real Developers

Update: I have managed to reproduce the issue and have reported it to the Development team. I will update you once it is fixed.

Regards,
Elton S

5

Any progress on this issue?

I’ve been able to verify that if I have a menu item that opens a DataView directly, having a screen field is fine. Since the screen field is a drop-down to allow me to choose a filter, and since the query is live, changing that value redisplays the DataView properly.

On the other hand, adding a screen field to a DataView that is in a form page doesn’t work at all. It causes the DataView to not function. I hope this is what you have also verified.

My aim for this screen field is to be able to type text in it, and have the DataView scroll to that item alphabetically, similar to using the search box in a form list. I don’t even know if that is possible, but the first step is to get the screen field to even work.

6

Hi Ron,

This issue has been resolved and is scheduled to be included in the next release, which is currently undergoing testing.

Thank you for your patience and understanding.

Regards,

Elton S

7

Any update on next release date?

Also, how do I find which version of Five I am using? Is there something on my Dev page that shows version?

How do I find release notes on the various versions?

Thanks…

8

Hi Ron,

We are ready to update you and give you a new release. Please notify when you are ready for this as your development account will come down for a few moments. I will provide you with the release notes.

The version number is in your profile menu by clicking the Five logo:

image
image350×421 16.1 KB

Please let me know when you are ready to be upgraded.

Kind regards,

Jo

9

Thanks Jo

I’m away from my Mac so you can do this any time. Please reply when done with version number and release notes.

Thanks…

Ron Mittelman

10

Hi Ron,

This has now been implemented in your upgrade.

Kind regards,

Jo

11

Hi Ron,

I have upgraded your account and emailed you the release notes.

Kind regards,

Jo

12

Thanks Jo.

This now works perfectly, and I can start typing a name in the DataView’s screen field and it will refresh the DataView appropriately.

This is because the query has 2 parameters now, one for the UserKey and one for the SortName:

SELECT
  `SelectedMembers`.`UserKey` AS `UserKey`,
  `SelectedMembers`.`MemberKey` AS `MemberKey`,
  `SelectedMembers`.`SortName` AS `SortName`,
  `SelectedMembers`.`IsSelected` AS `IsSelected`
FROM
  `SelectedMembers`
-- WHERE `UserKey` = ?
WHERE `UserKey` = COALESCE(NULLIF(?, ''), '10000000-0000-0000-0000-000000000001')
AND SortName LIKE CONCAT('%', IFNULL(?, ''), '%')

ORDER BY
  `SortName` ASC

At first I created a function to modify the variable and called that from OnExit of the screen field. this was very cumbersome and prone to delays before the DataView refreshed.

Luckily my good friend ChatGPT informed me that I can use the screen field’s Linked Parameter property to specify the five variable to set or change. Now the DataView seems to refresh as I’m typing, which is much more desirable than having to tab out of the field.

Thanks again!!!