Select Multiple Values in Lookup Field

In my Reports table, in addition to ReportsKey and ReportName fields, I want a field showing which Members should get the report by default. This is a default list, and can be changed at run-time.

In my many-to-many relationships, I learned how to put a field in and make its type lookup unique. Would I do the same here, even though it’s not really a many-to-many relationship (I don’t care from the Members side what reports they get)? Or is there a better way to have a one-to-many relationship? It’s confusing, because it is simply a transient list of members who get each report by default. It doesn’t need to be remembered after I print the report.

I do, however need the relationship to be such that it’s easy to query and write into other tables using SQL (so not an array or such).

Hopefully I’ve made my question clear…

EDIT
Forgot to mention, I will have the same issue with which study groups get this report by default, and which portfolios get the report by default. So if it requires a join table, I would need to add 3 of them.

EDIT Redux
Please remember I want to have DEFAULTS that won’t change, even if I temporarily change them for the current print run. I don’t want the defaults themselves to change.

Hi @RMittelman ,

Apologies for the delay in the response. We discussed this internally, for now, there is no easy way to approach this inside Five, but as discussed, we are aiming to improve reports and in the coming update, we are planning to add a field that will allow you to select multiple records, which should work with default values as well

Let me know if you have any other questions regarding this.

Thanks @Pranoy. I am in the process of signing up for beta program, so maybe we can figure this out. Meantime, I have some ideas I will try. If possible, can you just answer the following, and I will work on this from my end?

1: Regarding the original question: If I create a join table between reports and members, will this work? I have no need to see what members get what reports, but a join table would let me add multiple members to each report, with lookup-unique type of connection. That would let me choose multiple people to receive this report by default.

2: If this works, I would also add join tables between the report and the Profiles table and between the report and Study Groups table for those defaults. Would these extra join tables cause inefficiencies in the database?

3: Can you help me with a question about functions? When I create one and go to code window, it defaults to 3 arguments in the function call. Can I add my own arguments? If so, do they need to be after the existing 3?

4: I will have a Reports form bound to the Reports table, for the purpose of modifying that table. For the run-time version of the form, is it possible to base the form on the Reports table, but not the form fields themselves? This run-time form would let me modify the fields for running the report itself without actually changing the table. I do this in Access and it works well. I would need a list of reports on the form of course, but I’d need to use an event triggered by choosing a report from the list to load the various form fields. Is this doable? Is it possible to create a very small fdf to show me how to do this?

Thanks…

Hi @RMittelman

  1. Join Table Suggestion:
    A join table will allow you to add multiple members to the reports, so theoretically, it should work. You can try using a lookup, but I’m not sure how it will behave in your use case. I suggest to try it with a join table, and if you encounter any issues, feel free to reach out.
  2. Table Efficiency:
    No, those tables should not cause any inefficiencies.
  3. Default Arguments and Sub-functions:
    The three default arguments are required for your base function. You can add your own arguments before or after the existing three, and it won’t matter. You can also create sub-functions inside your base function. For example:
function BaseFunction(five, context, result) {
   // This is my base function that needs the three default arguments 
   const a = 4
   const b = 6
   const c = subFunction(a,c)
   return five.success(result);
}


function subFunction(argument1,argument2){
   // this is my sub function that I can call in my base function
   return argument1 + argument2;
}
  1. I’m not sure if I understand this correctly, but your reports can be based on a table, and the fields in the form for that table are customizable. For example, if I have a report where the data source is a ‘Product’ table in my database, the form for the product table can still be customized.

    1. Navigate to your form by going to Visual → Forms
    2. Click on the ‘Pages’ Tab, then select ‘General’
    3. Now, click on the ‘Fields’ tab. Here, you can delete or add custom fields to your form by editing any existing fields or clicking the ‘+’ button in the right corner.


Can you elaborate a little more on this if this doesn’t help you, then I will be able to send you an FDF which covers how to do it in Five.

Thanks for the reply.

In our Zoom training session, I showed you in Access I have a Setup Reports form. This is bound to the reports table. Same as in Five. I mentioned that I also have a Generate Reports form, which lets me choose which report from a list. I understand that I can add fields to this form and NOT base each field on a table column. Once the report is chosen, event code causes all fields to be populated with the data for that report. Unlike the Setup Reports form, the indifidual fields aren’t bound to the particular table fields. Instead, I populate them via an event which would run a function, which would populate each field manually from underlying data. I do this because I don’t want any changes to write back to the database. I want the values to be there so when I click the “Generate Report” button, it will then populate some temporary tables via SQL statements. Then the actual report will use those temporary tables to render the report.

This would require using some stack or context variables to populate the fields. I know this sounds very complicated. If instead, there was a way to disable the Save button on this new form, then I could make a more typical form where all fields are bound to the data table fields. I just don’t want to save any changes made on this new form.

Either way, when I click the “Generate Report” button on this new form, I need a function to run SQL statements to populate my fields in the temporary tables, then tell the report to render. Since the report is now based on a table, it should show the correct data. I would also need to disable certain fields on the form, and on the other “subform” pages, disable them if necessary. For example, the Reports table has a boolean field called “AskGroups”. If this field is false, then on the new “Generate Reports” form, that page for the study groups would be disabled.

I realize this is complicated, and I will be happy to arrange a call if needed. I think I know how to design the new “Generate Reports” form, I just don’t know how to write the code to populate the fields (that are not bound to table fields) when I select a report, or populate the temp tables when I click the “Generate” button. To clarify last sentence: I do know how to write the SQL statement to modify table data, just not how to refer to the current values on the form so I know how to construct the SQL statement with the proper values.

Please look at the Setup Reports form in the attached fdf file so you can see what I’m working with.
BrandeisConejo-20240509-0225431951347.fdf (4.5 MB)

To summarize:
1: Can I disable the save button on a form so I can use that form to make choices without saving back to the database?
2: If not, can I define fields not bound to the database table columns, and populate them in a function from variables from the underlying table?
3: How do I refer to the form fields in a JavaScript function which was called by a button on the form?

Thanks for reading this very long reply!

Hi @RMittelman

I went through your FDF and thank you for providing such a detailed response

  1. This is not a feature yet, to disable the button but you can make the entire form a read-only I am not sure if that satisfies your use case

  2. and 3. : Yes you can populate fields that are not bound to the database table with functions for eg:

    1. Start by creating a function from Logic → Code Editor
    2. Give it an appropriate name and now in order to refer to a field from your form you simply have to follow this syntax: five.field.[ID of the field] ,

let’s take an example, in your FDF I added a dummy field in the form Board Groups and gave it an id of Field1

if I want to populate this field from a function the code would be:

function PopulateField(five, context, result) {
    five.field.Field1 = 'Text that populates the field'
    return five.success(result);
}

I am attaching an FDF with these changes for your reference, Let me know if these simple examples works for you or if you would want me to elaborate more on this
BrandeisConejo-20240513-0641376253996.fdf (4.5 MB)

Thanks for the update Pranoy.

1: The form I want to use for running the report should display the default values for that report as stored in the Reports table. These values may need to be changed at run-time but not saved back to the database. This is why I asked about disabling the Save button. I’d guess that making the form read-only would also prevent me from making any run-time changes to the form fields, right?

2-3: Thanks for pointing out how to refer to the form fields. I can use that when I’m ready to tap the Run Report button. But I also need to know how to refer to database field values. Your PopulateField example only shows how to populate literal text to the form. I need to load it from the database ‘as if’ the fields we’re bound to database fields. What syntax do I use to refer to database field values in my function? And what event would I use to call the function?

As an alternative to number 1, is it possible that I can use the normal form instead (the fully bound one)? If I add a Generate Report button to the form which runs code to fill temp tables and tell the report to render, can I also code that function to cancel the update as if I had clicked the cancel button?

Thanks…

Ron Mittelman

Please, when you have the time, reply to my issue. I can’t really do anything more until I know how to do it. I’m happy to discuss this in a Zoom meeting at your convenience. I will be leaving for a vacation on Thursday (Pacific time) for 2 weeks. I hope we can make some progress before then. Thanks…

Hi @RMittelman

Apologies for not responding earlier. I’m not sure how I missed this.

  1. Yes if we make the form read-only that would prevent you from making any run-time changes

  2. The easiest way to do this in Five is to use queries to get data and then populate the form fields with the query results. I’ve attached an FDF of your application with an example of this.
    BrandeisConejo-20240520-0135321600627.fdf (4.5 MB)

    1. I have added a new form called Report Test that works on top of the Reports table. In this form, I’ve added four extra fields not connected to the database: Member Name, Phone Number, Address, and Group Name. The idea is to temporarily fill these fields with the values of a particular member from the database.

    2. To achieve this, we first need a query that returns the required details for the members. I’ve added a new query called “TestMembersQuery” Which basically is getting the required data:

SELECT
  Members.MembersKey AS "Key", //primary key as the first field
  Members.FullName AS "Name",
  Members.Address AS "Address",
  Members.PrimaryPhone AS "Phone",
  StudyGroups.GroupName AS "GroupName"
FROM
  Members
  INNER JOIN StudyGroupMembers ON StudyGroupMembers.MemberKey = Members.MembersKey
  INNER JOIN StudyGroups ON StudyGroupMembers.StudyGroupKey = StudyGroups.StudyGroupsKey

Note: Make sure to keep the Primary Key as the first field for this query as Five will need to refer to the primary key to fetch data.

  1. Now let’s go back to the “Report Test” Form and add a new field that is able to get data from this query, So we click on “Visual” → “Forms” → “ReportTest” → “Pages” → “General

  2. Now we added a new field inside this form called “Get Member” that has a display type of “Lookup Query” which will be referencing the “TestMemberQuery” from point 2.


  3. On the Events tab for the “Get Member” field, I’ve attached the ‘Populate Fields’ function to the “On List Select” event. This function will execute whenever you select an item from this lookup:

function PopulateField(five, context, result) {
    const data = five.metadata.ReportsTest.MemberLookup;
    five.field.Address = data.Address;
    five.field.MemberName = data.Name;
    five.field.PhoneNumber = data.Phone;
    five.field.GroupName = data.GroupName;
    return five.success(result);
}

If you run the application and select the Report Test form, it should have a lookup showing all your members

Selecting any member will fetch that member’s data from the database and populate the fields.

What happens is that now we have a lookup that references a query, selecting anything from it will put that query’s data in five.metadata automatically. That’s why I specified keeping the primary key as the first field when writing the query. Since the data is pushed into that object, we just execute a simple function whenever we select the list, which takes the data from the metadata and puts it in the fields.

Of course, you’re not limited to this approach. I’ve included another example in the FDF that uses a button to populate data. Instead of a lookup, it’s a separate form called Report Test 2, which is very similar to this approach with only a few changes.

Please let me know if you have any other questions or if you’d like me to elaborate further on any aspect