Select Multiple Values in Lookup Field

[RMittelman]

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.

[Pranoy]

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.

[RMittelman]

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…

[Pranoy]

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;
}

4. 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.

[RMittelman]

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!

[Pranoy]

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)

[RMittelman]

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

[RMittelman]

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…

[Pranoy]

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.

3. 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”
   

   

4. 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.
   

   
   
   
   

5. 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

[RMittelman]

Thanks for answering Pranoy.

This technique will be helpful if I use 2 Reports forms (one to maintain the reports table, and one to generate the report at run-time. That way I could open the second form, make any changes to the fields, then run the report based on the now-current values of the various fields. This should work.

Please advise on final question, however:
Is it possible, in function code, to cancel any updates to a form, as if I had pressed the “X” button to cancel changes? This is easy to do in Access. If I can do it in Five, then I could maintain AND run my reports from a single form. If I select a particular report from the list, and make changes, I’d click the Save button to, well, save the changes. Instead, if I select a particular report, then make some changes, then click the “Run Report” button, I could use the now-current values to cause the report to generate, and also in that function I could cancel the changes made to the form after the report was generated. This would only be viable if code can cancel the changes. It would be much easier than loading the values as explained by you earlier.

Thanks again…

[Pranoy]

Hi @RMittelman

Yes it is possible to simulate the cancel effect you through a function like this

function CancelForm(five, context, result) {
     five.selectAction(five.actionID())
    return five.success(result);
}

This function essentially resets the form. This is one way to achieve this effect. Let me explore other potential approaches, and I’ll get back to you if there is a better solution.

[RMittelman]

Thanks @Pranoy
This looks promising. At this point, I don’t know what the value of five.actionID() is, but I will take your word for it that it will “cancel” the changes.

What I would like to have happen depends on the particular form/page I am on.

On the General page of the SetupReports form, I will add a button called GenerateReport. It will, among other things, take the various values on the form and use them to update temp tables, which will become the source of the report data. Then it will both cancel the update and cause the report to generate. Not sure what order for these last 2 things. Only know they should be done AFTER updating the temp tables from form fields. Note that if I use a separate copy of the Reports form, where fields are NOT bound to data fields (as described earlier), I won’t need to do the cancel update logic.

If clicking a record in the list sub-form, I want it to toggle the IsSelected field of the clicked record, then save the record via code, then somehow cancel the action which would normally display the record I just clicked. Alternatively, it could run SQL to update the table record and refresh the list form page instead, then I wouldn’t need to save a form record.

Therefore, I need help with saving the changes in the sub-forms. I tried creating a function on one of my sub-forms (pages). The function code right now only pops up a message telling me it was clicked. I’m not sure how to do this. I tried an alert() statement, but that won’t work when using local copy of application. It gives an error saying “alert is not defined”. I changed the statement to console.log(), but I don’t see anything happening. Don’t know how to find the log for the running version not on my browser.

Can you possibly help with sample code showing how to
1: Cancel the edit (if any different than the above example),
2: Save the record if I make a form field change in code,
3: Ignore or cancel the normal behavior of showing the record when I click a record in the list (of the sub-form page)

Thanks so much for all of the help.

FYI: If you are not able to answer by Thursday (US Time, which I believe is 18 hours behind you), I will be on vacation for 2 weeks, so may not be able to reply.

[Pranoy]

Hi @RMittelman

Thanks for explaining your use case, I think using screen fields can be of help here. Screen fields are essentially fields for your report where you can input data, and it will be reflected in the reports. For example:

1. Start by going to reports under Visual → Reports
2. Select the desired report and click on the “Screen Fields” tab.
3. As an example we are going to add a single field here called “Name” with a display type of “_Text”
   
   
4. Now go back to the 'General" tab and click on “Template” to start editing the report
5. You can put the data from the fields in your reports through “Tags”
   
   

From the screenshot, you can see that at the top, there is a variable called “Name,” which refers to the field we just created in point 3. Clicking on that will insert the variable containing the data from that field into the report

In your final application now your reports will look like this :

First, we will retrieve all of the fields we added. Since we only added one field, only that single field will be displayed. Then, we click on the icon. Our report will generate with the value we passed, without storing it in any table and without writing any code.

You can see from the screenshot that the value now appears at the top of my reports because that’s where I placed the variable.

For 2.7, we are working on making the reports a better experience, so let me know if this works for you or not.

I am also attaching an FDF with these changes that have been applied to the “Study Group Roster” Report
BrandeisConejo-20240522-0618294234656.fdf (4.5 MB)

[RMittelman]

Thanks so much for the quick reply. I can see a use for this technique. I will keep screen fields in mind.

However, you have not answered several questions which I included near the bottom of my earlier comment.

I need to know how to cancel the edit via code if I’ve made changes on the form. You already showed me one method, but you said you were going to look for a better method.

I need to know how to update items in the sub-form page. Normally, if I click on a subform record in the list, it opens up that record. Instead, is it possible to intercept that click with an event, then do what I want in code, then close the detail form? 2 methods spring to mind: Either let the form open when I click the sub-form record, then on opening, run a function to swap the single field from true to false (or vice versa) then programatically save the record, which should also close the detail view of that record. OR, when I click a sub-form record in the list, simply run code to update the record using SQL, and then somehow stop the detail form from showing.

Thanks…

[Pranoy]

Hi @RMittelman

Hope you had a good vacation!
For method 1 I think it will not be ideal to monitor a form based on a boolean value that comes from a function, it can cause problems if there is a delay from the backend.

Regarding Method 2: Yes you can update records with SQL one way, an example syntax is:

     const divisionKey = `YOUR_GUID_HERE`
    let sql = `SELECT

                    Division.StreamlinedLimit

              FROM
                    
                    Division

              Where DivisionKey = ?`



    let queryResults = five.executeQuery(sql,0,divisionKey);


  
     if (!queryResults.isOk()) {
            
        return five.createError(queryResults);
    
    }  

In the example above we are executing SQL through five.executeQuery and if you want to pass parameters in your query you can do that by adding ‘?’ in your query and when calling executeQuery() just add all the parameters after the ‘0’ argument. In the example above we are passing a division key.
Now if you want to stop the detail form from showing you can use five.selectAction() function which accepts an ID for any form/process etc. Running this function will force the app to switch to any specified page after updating your SQL . Example of how your function can look like:

     const divisionKey = `YOUR_GUID_HERE`
    let sql = `SELECT

                    Division.StreamlinedLimit

              FROM
                    
                    Division

              Where DivisionKey = ?`



    let queryResults = five.executeQuery(sql,0,divisionKey);


  
     if (!queryResults.isOk()) {
            
        return five.createError(queryResults);
    
    }  


     five.selectAction(five.actionID())
     return five.success(result);
}

[RMittelman]

Thanks @Pranoy.

I appreciate the suggestions. I will try the one for executing my SQL code when I click a sub-form then cancelling and going directly back to the main page form.

Remember I suggested there are 2 possible ways to go when setting up / rendering reports:

1: Have a “normal” form based on the reports table, and a “generate report” form based on the table, but no fields are bound to table fields. Editing the report using the normal form would operate as expected. Using the generate report form would be slightly different. When the form loads, I would need an event to populate the fields based on a SQL query. The sub-forms for groups and portfolios would be loaded and their IsSelected fields would be set based on defaults in the Reports table. Clicking on a sub-form item would trigger SQL to swap the IsSelected field and return immediately to the parent form instead of opening up the sub-form record. A “Generate” button would generate the report using code.

2: Only have the “normal” form. Add a “generate report” button which would generate the report as expected. It would also have to “cancel” the edit in code, as if I had clicked the cancel link. You have not specified how this can be done.

First, I would like your recommendation for which method to use. I believe the first method may be easier to accomplish.

Second, I seem to have an issue getting to my development portal. I saved a shortcut to it in Chrome, which goes to “https://ron.5au.dev”. This is what happens:

Can you help me with this? Right now, I don’t remember which project is more current, the one on my PC using the Five app, or the online one.

Thanks…

[RMittelman]

Hi @Pranoy,

I am trying to get this working in my local application.
BrandeisConejo-20240610-1922205046078.fdf (4.5 MB)

Your example was helpful, but I can’t make it work. I put an event in the form which is called when I click on a study group record in the sub-form of the Setup Reports form. I’m sure it is because of my syntax, or maybe I chose the wrong event to us.

What I want eventually to have is a function called FlipIsSelected, and I want to provide parameters for TableName, ReportKey, and DetailKey.

• TableName should be assigned based on the name of the join table I happen to be using when I click on a sub-form record.
• ReportKey should be assigned from the current report showing on the General page of the form.
• DetailKey should be assigned based on the particular record I clicked on in the sub-form.

Note that in my Setup Reports form, I have a general page, a “Study Groups” page and a “Portfolios” page.

Currently, I have entered constant values for these items in the function that I created, but the desired object is to have these items passed in to the function(s) at the time I click on a sub-form record.

Also, this particular case is editing a join table being used in a page of a form. However, instead of this, I may need to do this in any table, so it would be best if the function could have parameters for the things I need, or simply calculate those values in the function based on the particular table and record being shown in the form. Does this make sense?

Can you please help me with this? Thanks…