Designing and Generating Reports Issues

I am (I think) operating in the beta environment, and trying to test designing and generating a report.

Generating issues:

I replaced most of HTML per Jason’s suggestion. When I generate the report, it keeps generating pages. I finally cancel the generation after 50+ pages.

Designing issues:

All of a sudden, I am having much trouble trying to change the layout of the general page by clicking the layout button at the top. Layout that I previously designed is gone, replaced by a default layout. All fields are in the “middle” column of the design grid, with empty columns to left and right. I cannot move a field into the left column, which I’ve always been able to do. Therefore, I can’t really custom-design my form.

Can we verify that I am in the beta environment, using the newest un-released version of Five?

I have uploaded my latest FDF to my OneDrive folder. Riley has the shortcut to that folder. If anybody else needs the shortcut, please email me at rmittelman@gmail.com. I do not want to publish the shortcut on the forum.

thanks…

Hello,

Jason will be in contact with you shortly,

Thanks,
Riley.

Thanks Riley. Jason informed me that he had not yet upgraded me to beta version of Five. However, he has since done so, and the problems still exist.

As a reminder, my latest FDF is up on my OneDrive folder if you need it. Thanks…

My report design is now going very well, thanks to Jason’s efforts.

I have a couple of questions on how to do things. Here is a partial report:

Here is my Access version:

Here are some questions:
1: Is there an easy way of putting the date on the report like the access version?
2: Is there a way of alternating the background of every other record like in the access version?
3: If not, is there a way of adding a small space between members? Leaving an extra space on the template seems too much space between members. Is there a way to have a div tag with a style having a smaller font size?
4: Is there a helper function that can display “Page x of Y” in the page footer?

Thanks…

UPDATE:
#1: Still need.
#2: googled a solution which said putting the following

tag around the member record table would work to shade every other row:

<div style="background-color: {{#_If @index % 2 == 0}}#efefef{{/_If}}{{#_If @index % 2 != 0}}#ffffff{{/_If}};">
    <table style="width: 100%;">
        <tbody>
            <tr style="font-size: 12px;">
                <td style="width: 30%;"><strong>{{MemberName}}</strong></td>
                <td style="width: 10%; text-align: center;">{{Position}}</td>
                <td style="width: 30%;" colspan="2">{{MemberEmail}}</td>
                <td style="width: 10%;"><br></td>
                <td style="width: 10%;">{{Paid}}</td>
                <td style="width: 10%;">{{CkNo}}</td>
            </tr>
            <tr style="font-size: 12px;">
                <td style="width: 30%;">{{MemberAddress}}</td>
                <td style="width: 10%;"><br></td>
                <td style="width: 15%;">{{PrimaryPhone}}<span style="color: rgb(17, 85, 204);"> {{PrimaryPhoneType}}</span></td>
                <td style="width: 15%;">{{SecondaryPhone}}<span style="color: rgb(17, 85, 204);"> {{SecondaryPhoneType}}</span></td>
                <td style="width: 10%;"><br></td>
                <td style="width: 10%;"><br></td>
                <td style="width: 10%;"><br></td>
            </tr>
        </tbody>
    </table>
</div>

However, in five when I try to render the report I get this error:

#3: I think I can do this by putting the following just below the member table:

<div style="height: 10px;">&nbsp;</div>

However, I would prefer to solve #2 if possible instead of using this.

#4: Still need this.

Hello,

  1. Could you add another grid column on the right of the header and add these values in and set the text alight to right?

  2. Take a look at this link How To Set Alternate Table Row Color Using CSS? - GeeksforGeeks … It shows documentation on how to do alternating table row colours with CSS.

  3. Glad to see you figured this one out!

  4. To add page numbers, you will first need to add a footer. After adding a footer, you will see a small icon within the report tool bar become available (it is a small page with a plus symbol located 4 from the right on the bottom row). You will then be able to add page numbers through here.

Thanks,
Riley.

Thanks for the quick reply Riley.

  1. I have a place for the date in the blue header area, I was asking how to insert a code that would evaluate to the current date.

  2. That is not what I need. I don’t want alternate table rows to be highlighted differently. Each member has a 2-row table just to organize where the fields go. I need the entire detail (member) section, including its entire table, to be highlighted on alternate rows. The suggested div tag shown above was suggested as a way to do this, but five doesn’t like it. Can you suggest an alternative? My FDF is uploaded.
    IS THERE AN ALTERNATIVE WAY TO OBTAIN ROW NUMBERS IN GROUPED RECORDS? IDEALLY THIS WOULD RESET FOR EACH NEW GROUP.

  3. Thanks.

  4. It’s unclear where in the template I need to be to add a page footer. At the very top? Inside the group by footer area below the {{/_Each}} for grouped records?

  5. Sorry, new one: The dates inside the member records are showing like “2024-06-01”, when they should show like “6/1/2024”. I used the Short Date display type which should do that correctly.

Thanks…

Hello,

  1. You could set a variable upon generating the report, this variable would then need to include the current date (i.e. five.now()), then you could display this variable within your report.

  2. I may need more of an explanation for this one.

  3. It will need to be added within the group by footer section.

  4. Where did you apply this display type? Alternatively, you could create a Javascript function within the report code and format the dates using that function.

Thanks,
Riley.

thanks again Riley.

You changed some of the paragraph numbers, so I’m addressing these using your numbers.

#1: So there is not a helper function to show the current date? I can use this suggestion for the report date, which is always generated “today”. However, please see #4 below.

#2: This one is complicated. I want to have every other row of the roster highlighted with a light gray background. I surrounded the member detail section of the HTML with a DIV tag containing conditional logic for the background-color style.

Here is a snippet of the html to illustrate this:

        <!-- member area -->
        <div style="background-color: {{#_If {{MemberSeq}} % 2 == 0}}#EFEFEF{{/_If}} {{#_If {{MemberSeq}} % 2 != 0}}#FFFFFF{{/_If}};">

            <table style="width: 100%;">
                <tbody>
                    <tr style="font-size: 12px;">
                        <td style="width: 30%;"><strong>{{MemberSeq}} {{MemberName}}</strong></td>
                        <td style="width: 10%; text-align: center;">{{Position}}</td>
                        <td style="width: 30%;" colspan="2">{{MemberEmail}}</td>
                        <td style="width: 10%;"><br></td>
                        <td style="width: 10%;">{{Paid}}</td>
                        <td style="width: 10%;">{{CkNo}}</td>
                    </tr>
                    <tr style="font-size: 12px;">
                        <td style="width: 30%;">{{MemberAddress}}</td>
                        <td style="width: 10%;"><br></td>
                        <td style="width: 15%;">{{PrimaryPhone}}<span style="color: rgb(17, 85, 204);"> {{PrimaryPhoneType}}</span></td>
                        <td style="width: 15%;">{{SecondaryPhone}}<span style="color: rgb(17, 85, 204);"> {{SecondaryPhoneType}}</span></td>
                        <td style="width: 10%;"><br></td>
                        <td style="width: 10%;"><br></td>
                        <td style="width: 10%;"><br></td>
                    </tr>
                </tbody>
            </table>
        </div>

My (sometimes) helpful AI client suggested the conditional style shown above. No comment on the veracity of that suggestion. Since I couldn’t figure out how to get a row index to use for the condition, I modified my SQL query (rqStudyGroupRoster) to include a row index which resets for each group. Apparently Five cannot parse the IF condition properly. I tried it with and without surrounding the data variable with double braces, and each way it generates an error. Can you shed any light on how to build conditional logic in the style element of that DIV that will work? As you can see in the table below that div, the {{MemberSeq}} works for the report, and no error is generated, so I know the variable is correct.

#3: Thanks

#4: I have a pre-defined display type that seems to work fine on Five forms, but perhaps not in the reports. The display type is called ShortDate. On forms that have a date field, it properly shows them as mm/dd/yyyy as we use here in the states. In the query definition, on the fields tab I gave that field a display type of ShortDate. Why does that work on a form but not on a report? Note that the report date (#1) is always “today” but the paid date for a given member record can vary. So a function seems best. Could you please suggest a function that I can put in the report code that takes an argument of what date to format? I could call it with five.now() for the report date and with the {{Date}} field from my GroupMembers query. Can you please post the function code and how I would call it at run-time?

Thanks So Very Much for all of your help. As usual, I posted my latest FDF on OneDrive.

Hello,

  1. There is no helper functions within reports related to showing the current date, the best method would be to set a variable within a java script function which is equal to ‘five.now()’ and then display this variable within your report.

  2. Give this code snippet a go and let me know if it works!
    "background-color: {{#if (eq (mod MemberSeq 2) 0)}}#EFEFEF{{else}}#FFFFFF{{/if}};"

  3. (This is for point 4). Here is a code snippet from another application I have made:
    function convertDateFormat(dateStr) {
    const [year, month, day] = dateStr.split('/');
    return ${month}-${day}-${year};
    }

(You cannot see it within the text for the function but you will also need to add a backtick before $ and after {year} in the return statement.

You may need to play around with the code to get it to look how you want (adjust the position of month, day, and year in the return statement. You can also adjust the ‘-’ to be whatever separator you would like within the return statement. You may also need to adjust the value used to split the date into its seperate components (i.e. you may need to use ‘-’).

You will need to add the function into the ‘Code’ section of your report. You can then apply it in your report by going:

{{convertDateFormat (DATEVARIABLENAME)}}

Hope this helps,

Thanks,
Riley.

Sorry, Riley. I tried editing the last post and it wouldn’t let me because it was there too long, even though the pencil icon was there. Please use this modified reply instead.

ISSUE WITH ALTERNATING ROW NUMBERS:
I tried your suggestion for background-color style and received an error message at run-time: "Missing helper: “mod”.

So I tried using a function for this instead.

function IsEvenRow(rowNum) {
    return rowNum % 2 === 0;
}

I’m having trouble calling this. My snippet HTML is:

<div style="background-color: {{#if (IsEvenRow(MemberSeq))}}#EFEFEF{{else}}#FFFFFF{{/if}};">

I’m not sure if I have too many parentheses here. I thought the IF needs parens of its own. Please advise

Unfortunately, it causes the error below:

ISSUE WITH CONVERTING DATE:
I tried your suggestion for using a function. The suggestion above looks backward to me, because I’m trying to change a yyyy-mm-dd date to a m/d/yyyy string. So here is the function I came up with, which doesn’t require specific string positions.

function ConvertDateToUS(dateStr) {
    const date = new Date(dateStr);  // Convert string to Date object
    const month = date.getMonth() + 1; // Months are zero-based
    const day = date.getDate();  // Use getDate() (local time)
    const year = date.getFullYear();
    return `${month}/${day}/${year}`;
}

Could you please advise on the feasibility of using your function, which looks for the dashes, and simply reverse your logic to expect a dash delimiter and output the date with slash delimiters in the right order?

Also, I get an error calling this function. I put the following in the cell for today’s date:

{{ConvertDateToUS(five.now())}}

This does not seem to work. Did you say I first need to capture the date in a variable, then use the variable?

If so, which event should I put this in? I already have code that runs when the user logs in, to capture the UserKey in a variable. I could add this variable assignment to that code also. Then it would be current for the current login session. Even though it’s possible for the user to log in one day then try to run the report the next day, my experience is that a session will not last that long, and if I try to use a session from the prior day it doesn’t work. So is this a good idea to just assign the variable at login time? Then would I call it with “five.variable.CurrentDateTime”?

LATEST FDF IS ON ONEDRIVE

Hello,

I currently do not have your One Drive link, if you are not comfortable providing it on here, email it to me at riley@five.co

I will take a further look at your FDF to resolve these issues,

Thanks,
Riley.

Hi @RMittelman,

ISSUE WITH ALTERNATING ROW NUMBERS:
Just a minor change at the parameter, please change the line to:

<div style="background-color: {{#if (IsEvenRow MemberSeq)}}#EFEFEF{{else}}#FFFFFF{{/if}};">

ISSUE WITH CONVERTING DATE:
Since you are getting today’s date, you may use the new Date() to get the date instead of pass in the five.now().

function ConvertDateToUS() {
    const date = new Date();  // Convert string to Date object
    const month = date.getMonth() + 1; // Months are zero-based
    const day = date.getDate();  // Use getDate() (local time)
    const year = date.getFullYear();

    return `${month}/${day}/${year}`;
}

Apply {{ConvertDateToUS}} in the report template.

Thanks.

Thanks @Vin-five. I will try your suggestion for the style tonight.

Regarding the date suggestion: As I explained to Riley, I need to use the function for multiple purposes. For the report date in the header I want today’s date. For the check date of each detail record, I want to send the Paid field value from the data source.

So I need a parameter for the function. How do I do this?

Since I have a function that runs at login time which saves other variables, I could add a CurrentDate variable. The question is how to call the function with that info. Do I put {{ConvertDateToUS(five.variable.CurrentDate)}} in the header?

Hi @RMittelman,

Thanks for clarifying.

To call the function with parameter, you may use {{ConvertDateToUS DateVariable}}, with a space between function name and parameter, and without the bracket symbol.

Since a variable will be assigned for today’s date, you can format it before assigning it. This way, there will be no need to call the function again for conversion.

Thanks Vin. So the syntax is to send the parameter(s) without the parentheses then?

Also can you verify how I do that for today’s date? I suggested creating a variable for the current date at login time. Can you please advise how to use the function in the page header with a variable?

Hi @RMittelman,

Yes, just ensure there’s a space between the function name and the parameter, and omit any brackets or commas.

The ConvertDateToUS remain to accept parameter like before

function ConvertDateToUS(dateStr) {
    const date = new Date(dateStr); // Convert string to Date object

    const month = date.getMonth() + 1; // Months are zero-based
    const day = date.getDate();  // Use getDate() (local time)
    const year = date.getFullYear();

    return `${month}/${day}/${year}`;
}

Call the {{ConvertDateToUS Paid}} in the report.

For today’s date, there is a function InitialSetup, I use it to set the variable as below:

function InitialSetup(five, context, result)  {
    
    // save userKey
    const userKey = five.currentUserKey();
    five.setVariable("UserKey", userKey);

    // save current date
    const date = new Date(five.now());
    const formattedDate = date.toLocaleDateString('en-US'); // Outputs: 2/5/2025
    five.setVariable('CurrentDate', formattedDate);

    return five.success(result);
}

In the report, put in {{five.variable.CurrentDate}}

The result will be like this (covered the report data, left the date for your view).

Hi Vin,

I appreciate your help, and the report is getting closer to what I need. However, there are still a couple of problems. Please consider this code:

function ConvertDateToUS(dateStr) {
    const [year, month, day] = dateStr.split('-');
    
    // const date = new Date(dateStr);  // Convert string to Date object
    // const month = date.getMonth() + 1; // Months are zero-based
    // const day = date.getDate();  // Use getDate() (local time)
    // const year = date.getFullYear();
    return `${month}/${day}/${year}`;
    
}

If I use {{Date}} in the SGPaid column of the template, I get 2024-06-01, 2024-06-09 and 2024-06-09 in the rendered report. I also verified these are the proper dates returned by the underlying query.

If I use {{ConvertDateToUS Date}} in the template, and the code is exactly as above, I get 6/1/2024, 6/9/2024 and 6/9/2024 as expected.

If I modify the function by commenting the first line and un-commenting the next 4 lines (so it agrees with your prior code example), I then get 5/31/2024, 6/8/2024 and 6/8/2024 in the first 3 records. So I think there is something wrong with the logic of the commented-out code. Also, I am worried that if a date with time, or a timestamp field is supplied to the function, it will not know how to separate the time portion out.

Could you please advise how to properly code the function, and protect it in case the time portion (or a timestamp) is sent?

Finally, I seem to have a bigger issue here. When I run the query itself (rqStudyGroupRoster) in design mode (after changing comments so literal user ID is used instead of ? placeholders), I get several records from 2 different study groups. However, when I run the report, I only get the first 3 records of the first study group. The balance of the data is not rendered for the report. This is a recent development, so may have something to do with the different style codes I used. I am worried that there is a data condition in the 4th record which is causing the balance of the records to not be rendered.

I am copying the latest FDF file to my OneDrive folder. Riley has the shortcut. If you need me to send that to you, please reply with your email address so I don’t have to publish the shortcut on the forum.

I have the OneDrive folder link and will take a look at the FDF.

Thank you.

Hi @RMittelman,

Here’s the sample function to extract just the date part from the parameter:

function ConvertDateToUS(dateStr) {
    const dateOnly = dateStr.split('T')[0]; // Extract only the date part (ignore time if present)
    const date = new Date(dateOnly); // Parse the date without time zones or time

    // Check for invalid date
    if (isNaN(date.getTime())) {
        return 'Invalid Date';
    }

    const month = date.getMonth() + 1; // Months are zero-based
    const day = date.getDate();
    const year = date.getFullYear();

    return `${month}/${day}/${year}`;
} 

To fix the issue with the report not displaying the expected results, do this:

  1. Remove {{#_Each GroupedRecords}} in line 85.
  2. Add the following code after closing the <div> in the report header (at line 87):
<div class="fiveReportHelper">{{#_Each GroupedRecords}}</div>

Thank you.

Thanks Vin. The report now contains all of the data. You don’t say why the fix works, but I’m guessing that the for each grouped record code was not working because it was inside of the div that identifies the header. What is confusing is when I need to put a div in in the html and when I can just use the codes by themselves by inserting them via helpers into the template. If I use the helper function in the template, does that automatically put in a div tag that I’d see if I switched to the html view?

Regarding the date: I use your code, but as before, the US-formatted dates are now a day early. The first 3 records say 5/31/2024, 6/8/2024 and 6/8/2024. They should be 6/1/2024, 6/9/2024 and 6/9/2024. There is something wrong with using a date object and pulling out the month, day and year portions. Sub-stringing the date part seems to work properly. Can you please advise why your date code returns dates a day early?

Thanks…