0

I need to retrieve all of the timecards invoiced on a particular sales invoice. I have tied all of the objects to get the link together. Sales Invoice->Billing Event->BE Item->Timecard Header->Timecard Split. I am very experience with SQL, but a little less so with SOQL. I am wondering what the most efficient way to retrieve the list of timecards. Is there any way to use a combination of joins or nested where clauses to pull them in one select, or is the best way to use separate selects to pull the relevant ids for each object? Any suggestions on the best way to structure this will be appreciated.

Update .....................

I was able to get a query to pull the Timecard Split details, but I had to start with the billing event id. I need to create a controller to feed a VF page to display the details for all timecards billed on a particular sales invoice. I want to create a button on the SIN to initiate the process, but I'm not sure how to get the BE id passed into the controller. I have a simpler example to use as a template, but I just need some help to get started. The query I have created is below:

select  id, pse__Resource__r.Name, pse__Start_Date__c,
  pse__Sunday_Hours__c, pse__Monday_Hours__c, pse__Tuesday_Hours__c, pse__Wednesday_Hours__c, 
  pse__Thursday_Hours__c,pse__Friday_Hours__c,pse__Saturday_Hours__c 
from pse__Timecard__c where id in
  (select be.pse__Timecard_Split__c from pse__Billing_Event_Item__c be
where pse__Category__c='Timecard' and be.pse__Billing_Event__c = 'a344O000001hBUX')

I have done a lot of apex but only a little VF pages and components. I'm not sure what object I need to create to feed the component. I need to explode each day that has hours entered to individual lines showing the daily time details for each resource. Once I have that I will be able to handle the printing to pdf.

2
  • don't use hard-coded IDs in queries as they won't work when you deploy to other environments
    – cropredy
    Sep 21, 2020 at 0:10
  • I understand that. This was the code I used in Dev Console to test the query. I had originally had another level to select the BE from the SIN object, but as you stated I was only able to go down one level. I was making sure I could get the data I wanted. I could not figure out from your initial response how to get down to what I wanted by starting with the SIN id. Sep 21, 2020 at 17:36

1 Answer 1

1

SOQL only supports going down one level; but, if all you need are a list of TimeCards, you could do in a single query:

SELECT Id, fldA, fldB,
     BE_Item__r.BE_FldA, ... ,
     BE_Item__r.Billing_Event__r.BE_FldA, ... ,
     BE_ITEM__r.Billing_Event__r.Sales_INvoice__r.SalesINvoiceFldA, ...,
     (SELECT Id, TimeCardSplitFldA, ... FROM TimeCard_Splits__r)
  FROM Timecard_Header__c
  WHERE ...  // filters on TimeCard Header and/or any of its parents

Using your query in OP:

select  id, pse__Resource__r.Name, pse__Start_Date__c,
  pse__Sunday_Hours__c, pse__Monday_Hours__c, pse__Tuesday_Hours__c, pse__Wednesday_Hours__c, 
  pse__Thursday_Hours__c,pse__Friday_Hours__c,pse__Saturday_Hours__c 
from pse__Timecard__c where id in
  (select be.pse__Timecard_Split__c from pse__Billing_Event_Item__c be
where pse__Category__c='Timecard' and be.pse__Billing_Event__c = 'a344O000001hBUX')

change the filter to get all the timecards for a specific Sales Invoice to:

select  id, pse__Resource__r.Name, pse__Start_Date__c,
  pse__Sunday_Hours__c, pse__Monday_Hours__c, pse__Tuesday_Hours__c, pse__Wednesday_Hours__c, 
  pse__Thursday_Hours__c,pse__Friday_Hours__c,pse__Saturday_Hours__c 
from pse__Timecard__c 
where pse__TimeCard__r.pse__Billing_Event__r.pse__Sales_Invoice.Id = :someInvoiceId

Where:

  • someInvoiceId would be the sales invoice Id from the cirrent detail record that the button was clicked on. Your controller will have this in the controller object's getId() method.
  • I may not have the relationship names to the parent records correct as this is a managed package that I don't use. But Workbench will show you the correct relationship names to use
6
  • Thanks for the help. I have updated the original post with current status and follow-up. Sep 21, 2020 at 0:08
  • a really useful VF reference is: VF in Practice - I'd suggest you start there as you are really asking the community to write your apex controller and VF page, see How to Ask
    – cropredy
    Sep 21, 2020 at 0:12
  • @JerryWelliver - updated answer
    – cropredy
    Sep 21, 2020 at 22:56
  • Excellent! I have successfully created the controller and the skeleton VF page. My only stumbling block now is how to create the value for the pageblocktable. I have done tables in the past based on straight selects from custom table objects, but in this case I need to preprocess the selected rows and create up to 7 rows per timecard (one for each day). Do I need to create a custom object in salesforce to use as the template for the list or can I create a memory only list structure? Sep 23, 2020 at 1:06
  • Google "Salesforce wrapper class controller" for many ideas. @JerryWelliver
    – cropredy
    Sep 23, 2020 at 2:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .