Accessing Data Outside Of An Authentication

[jeffs88keys]

I have an app that records sales 40 different sales people.  Each person has access to their own sales reports and history based on an Record Level Security set to their "salesperson_id" field.

 

Is it possible to calculate and store a ranking of each salesperson vs. their peers either at the agent authentication or at an admin level authentication?

 

I think it would be best at the admin level to feed a table that can be drawn from at the agent level, but I'm not sure how to calculate and store the ranking data. 

 

Thanks in advance.

[Jan]

Hello,

 

I would ask several additional questions.

 

If I understand correctly, you have two tables:

- Sales_People,

- Sales_Reports,

And every report has a link to a Sales_People table.

Is it correct?

 

Then in I have a question - how you want to rank people?

- By number of reports;

- By the sum of values of a field in the Sales_Reports, like Sum;

- By another way

 

Do you want to rank people:

- by the whole table Sales_Reports;

- by periods From... To;

- by another Criteria.

 

And do you want to store only one rank to the table? I mean, when a new rank is created, the old results are replaced.

Or do you want to store every rank and date, when it was created?

[jeffs88keys]

Great questions, and thanks for following up .....

 

For the 2 tables, yes you are correct.  The unique id of the salesperson (from the sales_people table) is stored in the sales_report table as a field in each of the sales records when the record is created through the submission form using record level security back to that id.

 

The people will be ranked on the basis of the Sum of their "sales_price" (a field stored in the sales_report table)

 

We would like to be able to rank them by periods, specifically month to date and year to date

 

As far as storing the ranks to the table (a different table, I would presume)  I think the easiest would be to replace the data.  The concept is for each sales person to be able to see their given rank at the point in time they are logged in, not necessarily historically.  From a management standpoint, I can see historic rankings by running our bar graph at any time, so historic is not necessary (sorry for the long answer to a short question).

 

Again, I appreciate your help.

[Jan]

Hello,

 

If you agree to replace values, you can add one more field to your "sales_people" table. Then you can easily display it to users, because this value will be displayed in the Parameter Picker in the Authentication Fields section.

 

I have found how to change values of the Rank manually. It is not the best solution and I will look for autosolution, but maybe it will be useful too.

 

I have used the following steps:

1) Create a Search and Report DataPage, DataSource is the "sales_people" table, select "Enable Advanced Options" and "Enable parameters" checkboxes.

2) On the "Search Type" step, select "Allow users to select data using a search form"

3) On the "Select Search Fields" step, select any field (it will be hidden, Virtual fields are needed on the Search Page).

 

4) On the "Configure Search Fields" step:

4a) Select the "Hidden" Form element for the field from step 3.

4b) Add two Virtual Fields, check "Calendar popup", check "Required" for both (it is not mandatory, but the Rank does not work, if one of fields is empty).

4c) Change labels - for Virtual1 enter "From" and for Virtual2 enter "To".

4d) On Advanced tab, check "On exit" for both fields.

 

5) On the "Results Page Editing Options" step check "Inline edit" (it allows you to change values of the "Rank" field).

6) On the "Configure Results Page Fields" add a Calculated field and enter the following code:

select sum(sales_report.sales_price) from sales_report where sales_report.link_to_salesperson=target.[@field:id] and sales_report.sales_date>=convert(DateTime,'[@Virtual1]') and sales_report.sales_date<=convert(DateTime,'[@Virtual2]')

Please enter the name of your table instead of "sales_report" (five times is used in the code),

the name of your fields instead of "sales_price", "link_to_salesperson", "sales_date" (two times is used in the code),

the name of your field instead of the "id" - it is the field from the "sales_people" table.

 

7) On the "Results Page Options" step, select the Calculated Field in the Default sort order drop-down list.

 

Now, when you open the page, you can add (and change) "Ranks" for every salesperson and then display it to the salesperson as the Parameter from Authentication Fields section.

 

Also you can add the second field, for example, Count and enter the number of sales person to it.

Then you can display to a salesperson the message like:

Rank is [@authfield:Rank] of [@authfield:CountPersons]

 

I hope, it helps.

And I hope, I will post the solution where the "Rank" field will be changed automatically

[jeffs88keys]

Jan.... Thank you so much for your help.  it took a little bit of adjusting, but I got it to work !!!!   (I had to uncheck the "On Exit" from step 4d to get the sales to calculate).

 

it would be great to have the Rank field propagate automatically, but baby-steps.

 

I'm very appreciative of your expertise, as well as the time you took to help me out.  

 

Thank you !!!!