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
Steven HoughtalenSteven Houghtalen 

How to create list (using apex) of clients with first time services in current fiscal year

I have a problem I don’t know how to solve and hoping someone can help.  I have an object which is a services log for clients that have had services.  The service log contains customer ID, date of service, type of service, etc.
I need to create a list of unique client IDs who have had services last month for the first time in the current fiscal year.  I have to run this on a monthly basis so dates can't be hardwired.
Hopefully someone has a brilliant solution.  Thank you.
 
Best Answer chosen by Steven Houghtalen
Alain CabonAlain Cabon
SOQL: LAST_MONTH,  LAST_FISCAL_QUARTER are not suitable.

Only THIS_FISCAL_YEAR() and FISCAL_MONTH() are interesting.

The "NOT IN" is not interesting because semi-joins and anti-joins cannot use the same object. (it is not SQL)
You cannot query on the same object in a subquery as in the main query. (SOQL documentation)

The "NOT IN" could be replaced with a used flag or a date updated after the monthly query (batch).

You must add a relation between the contact and the service_log__c.
  
SELECT contact__r.name
FROM service_log__c 
WHERE FISCAL_MONTH(date_of_service__c) = 12
and contact__r.flag_used = false
GROUP BY contact__r.name

After the extraction of the data, you have to update flag_used or date_used into the contacts (ie: you clients ?) with flag_used = true.

That could be something like that but I don't test the solution.

Alain

All Answers

Tim BarsottiTim Barsotti

Does the services log have a master detail relation to the client or a lookup? If MD, you could create a rollup summary field on the client for the MIN date of service. Then you could query clients with date > XX days ago. This would be easiest.

Otherwise, you will want to query all client + first service records older than XX days. Get the IDs of the clients and then query clients + service records that are NOT in the first set.
Chuck HannahChuck Hannah
Another approach might involve comparing a COUNT on services LAST_MONTH to a COUNT of services in your fiscal year range. If last month's count is equal to the fiscal year's count, that'd contain their first service...depending when you run the report, you may need to also consider (ie- subtract) the services for THIS_MONTH.

The trick I see here is keeping up with the fiscal year. Salesforce doesn't know that unless you tell it somewhere/somehow...best way might be to create/leverage a global/static class.
Steven HoughtalenSteven Houghtalen
Thanks Tom,  not sure I understand the first approach.  Let me give you a specific example to see if we are on the same page.
The fiscal year starts on 9//1/16.  I will execute this method on  1/12/17.  Note that some clients will have records in the service log for the month of January.  To be specific to this scenarion,  I need to know what clients had a service(s) in the month of  December but had no services in the time from from 9/1/16 to 11/30/16.  

Question: Does your first solution work with the above scenario?  With respect to this scenario, what is MIN date of service?
Question: How do you do this: query clients + service records that are NOT in the first set.
Question: How to construct a formula to give you the first day of the current fiscal year?
Question: How to construct a formula for the first and last day of last month?

 
Alain CabonAlain Cabon
SOQL: LAST_MONTH,  LAST_FISCAL_QUARTER are not suitable.

Only THIS_FISCAL_YEAR() and FISCAL_MONTH() are interesting.

The "NOT IN" is not interesting because semi-joins and anti-joins cannot use the same object. (it is not SQL)
You cannot query on the same object in a subquery as in the main query. (SOQL documentation)

The "NOT IN" could be replaced with a used flag or a date updated after the monthly query (batch).

You must add a relation between the contact and the service_log__c.
  
SELECT contact__r.name
FROM service_log__c 
WHERE FISCAL_MONTH(date_of_service__c) = 12
and contact__r.flag_used = false
GROUP BY contact__r.name

After the extraction of the data, you have to update flag_used or date_used into the contacts (ie: you clients ?) with flag_used = true.

That could be something like that but I don't test the solution.

Alain
This was selected as the best answer
Steven HoughtalenSteven Houghtalen
Thanks Alain, this was very helpful.  I was hoping i would be able to do this through a SOQL join without processing record by record.  
But, what you recommend is pretty straight forward to implement .