function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
splashgordonsplashgordon 

Defining custom global variables

Is there any way to define a custom global variable (or re-purpose an existing global variable)?  The issue we have is that we have a large number of reports (c. 40) that refer to a certain date.  That date needs to be changed each week after we have done some manual data loading using data loader.  Our current process is that one of our users manually goes into each report, changes the date and saves the report.  This is a time consuming process and error prone.
 
A better solution would be to have a global variable defined that is referenced by the reports and then each week they simply need to change the value of that global variable.
 
Has anyone come across this sort of requirement before or have any ideas how this could be achived?  The date is actually referenced in both the filter criteria and the timerame criteria of the reports.  However, even if the solution fixed one of those that would be very helpful.
 
I look forward to any advice you can offer.
 
Thanks!
Gordon.
David GorslineDavid Gorsline
Have you considered a custom object? For instance, you could set up an object called Reporting Calendar with one field called Report Effective Date, or somesuch. I'm not sure that you'd be able to use the info in this object in all the contexts that you require, but consider this a starting point.
splashgordonsplashgordon
Hi David and thanks for your thoughts. I don't think having a custom object for this variable will work as the field needs to be accessible from all objects as a global variable. Ideally we would be able to setup custom fields on the Organisation object and reference these in any report/object. If you have any other ideas I would be interested in hearing them. Thanks, Gordon.
Jeff TalbotJeff Talbot

Have you thought of using relative dates? I'm guessing that you have because it sounds like you need an exact date... but thought I'd mention it just in case you overlooked that possibility.

Search in Help & Training on "relative date" and look for the Solution "How do I create reports or views using relative dates?"
splashgordonsplashgordon

Hi and thanks for the idea.  Relative dates aren't an option unfortunatelt as we need an exact date (as you surmised).

The process we have is that we load timesheet data from a corporate system into salesforce on a weekly basis (every Tuesday).  The data loaded is up to the previous Sunday.  All the reports I am referring to report on that data in various ways.  The problem with using a relative data, such as give me a total of all hours "less than LAST WEEK" is that it would work on the Wed, Thu, Fri, Sat, Sun following the upload but, the moment we move into the following Mon and Tue (and before we do the upload again), it would be looking at a week with no data.

We therefore use the exact date of the Sunday that marks the end of the time reporting period.  This is referenced in around 40 reports and, as such, we have to update them all as part of the Tuesday process each week.  This is why I am trying to find a global variable solution so I can simply update the variable value in one place and have the reports refer to it.

Keep the ideas coming!

Cheers,

Gordon.

Message Edited by splashgordon on 11-09-2007 03:11 AM
SteveBowerSteveBower

I haven't given this much thought, but could you use a formula based on the date of upload to calculate a week number that's saved along with the data when you upload it in the first place?  Then your reports can use the higest week number instead of date based reporting.
Best, Steve
philbophilbo
I think this is best viewed from a general perspective, not just in the context of Gordon's 'date' issue.  A general-purpose repository of key-value pairs, that can be accessed/incorporated into formulae in any context, would be a most excellent feature indeed.  A natural possibility to be such a repository is the 'Organization' object, which is by definition a singleton per org and could easily be extended with any number of custom fields,  whose names+values could be made available across the whole org.
splashgordonsplashgordon
Steves post got me thinking... I now have a working solution which I will share with the community now as it may well help others out there :smileyvery-happy: Before I start I shoudl say that this solution is basically implementing a global variable for our "Timesheet" object.  I still think a global variable solution provided by Salesforce (using the organisation object) is a badly needed feature request.  I have voted for this on 3 ideas on the ideas exchange and would encourage you all to do the same if you agree (http://ideas.salesforce.com/article/show/43238, http://ideas.salesforce.com/article/show/30267, http://ideas.salesforce.com/article/show/57618/Global_Variable).
 
So, onto my solution...
 
(1)  I created a formula field on our timesheet object called "Report Start Date". This contains the absolute date I need for my various reports.  As it is a formula field it gets applied to all timesheet records
 
(2)  I created a series of formula fields to evaluate to dates based on this "Report Start Date".  So, for example "Month Start" is:
DATE( YEAR(DATEVALUE(Report_Start_Date__c)), MONTH (DATEVALUE(Report_Start_Date__c)), 1 )
 
(3)  I created a series of "Criteria" formula fields such as "Month Criteria 1" is:
IF( Work_Day__c >= MTD_Start_Date__c, "Yes", "No")
 
(4)  With all the above I am now able to change all my reports to include criteria like "If Month Criteria 1 = Yes" (instead of If Work Day >= 01-Nov-07)
 
(5)  All I have to do each week is to change the "Report Start Date" that kicks all this off.  I no longer have to spend an hour each week updating 40 reports that reference specific dates!  Hey presto!
 
I hope this helps others out there.
 
Thanks for all your feedback everyone and thanks Steve for planting the seed in my mind!
 
Cheers,
Gordon.