0

Build a Report Advice

Ok, I want to create a Form within my Ninox CRM that will allow a user to select pre-defined reports, click a button, and then than upon click it will create a new record for that corresponding report table.  I just want to know the best way to go about this.  I want to create form where the use a drop down and it bring up the predefined criteria, they set their date range, and click the button.  However, the problem I am running across is that when doing separate form views, it still shows every field for that Build a Report table.  I'm including some pictures for clarification.  I know how to use the buttons to create records, so I don't need any advice there.

5 replies

null
    • Fred
    • 11 mths ago
    • Reported - view
     said:
    then than upon click it will create a new record for that corresponding report

    I'm not sure why you need a new record created for a report? Most reports are dashboards unless you need to print something. Even then they can called from dashboards.

    Can you upload a sample DB so we can see the structure?

    • Dave_Irving
    • 11 mths ago
    • Reported - view

    Fred, what I do is create several reports for every week and store them in a corresponding table (example is in image above).  That way I have access to past reports and can easily export the whole table to create Excel charts to include in my monthly corporate reports.  However, before I only just had the tables with all the predefined formulas I needed and created a duplicate record with new dates for each week.  However, because of how many formulas that were in each of these tables, it became too overloaded.  So, now instead I want to create an option to manually generate the reports I choose and have it added as a record to the appropriate table where the fields are not formulas but number fields.  I can do this with a button.  However, I need to know how to know how to set this up correctly.

    So, I want to have a Drop down choice, select the report I need.  Then select the dates (either have all the formula fields visible or not), click my "generate report" button and have it create new record in corresponding table.  My whole goal is to create less load on browser/server in those tables and also make it more user friendly for non system admins to create the report records.

    • Fred
    • 11 mths ago
    • Reported - view

    I don't know the structure of your reports so I can't say if you can get away with just a select and a create function. Or if you will need a for loop or two in there.

    You will need to give us more information on what tables and fields you are getting the data from. How you want the data calculated (sum, avg, count, etc). What fields they are going to.

    Like I said, if you can upload a sample DB that would make this a lot easier.

      • Dave_Irving
      • 11 mths ago
      • Reported - view

      Here you go.  This is a backup with most of the data removed.  This is a backup from before I moved the report tables to under Report Builder.  In this backup, they are under Leads table.

      Sample DB.ninox
    • Fred
    • 11 mths ago
    • Reported - view

    The DB doesn't look like your pictures, but I think the basics are there.

    In the Stats -  Overall table:

    1) I changed the names of the Total Leads and Total Active Leads formula fields by adding "old" in front of them.

    2) I created two new number fields with the above names.

    In the Dashboard table > Statistics tab

    1) I added two new date fields, Start Date and End Date

    2) Then I modified the Overall Statistics button to:

    if 'Start Date' = null or 'End Date' = null then
    alert("Please select a Start or End date.")
else
    let xStart := 'Start Date';
    let xEnd := 'End Date';
    let getLeads := (select Leads where 'Lead Entered Date' >= xStart and 'Lead Entered Date' <= xEnd);
    let newRec := (create 'Stats -  Overall');
    newRec.(
        'Start Date' := xStart;
        'End Date' := xEnd;
        'Total Leads' := count(getLeads);
        'Total Active Leads' := count(getLeads[Status = 12])
    );
    openRecord(newRec)
end

    As you add new number fields to Stats -  Overall then you can add them to the list under newRec. Remember that you need a semicolon at the end of each line, except for the last line.

    You can see on lines 11 and 12 I reference the variable getLeads. This way I've only done 1 select statement then you filter the results to match whatever you need. For example, to do Total Enrolled you would do:

    newRec.(
        'Start Date' := xStart;
        'End Date' := xEnd;
        'Total Leads' := count(getLeads);
        'Total Active Leads' := count(getLeads[Status = 12]);
        'Total Enrolled' := count(getLeads[Status = 3])
    )

    You can the modify the main code to the other buttons so it creates the record in the proper table.

Login to reply

Content aside

  • 11 mths agoLast active
  • 5Replies
  • 132Views
  • 2 Following