How to extract records based on a date criteria | Community
Skip to main content
Jo_Pitts1
Jo_Pitts1
Community Manager
February 18, 2020
Solved

How to extract records based on a date criteria

  • February 18, 2020
  • 1 reply
  • 3442 views

Afternoon all.

I have a scenario where on a daily basis, I need to extract all leads that have had an expiry date reached.

It would be reasonable to assume that this will result in around 1,000 - 2,000 records each day needing to be extracted.

Is Bulk extract the way to go?  If so, how will I filter on the field in question?

Cheers

Jo

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by SanfordWhiteman

Depending on your subscription, you may not be able to use only Bulk Extract for this particular scenario.

 

If your BE supports Smart Lists then run an extract on a corresponding SL.

 

If your BE only supports Static Lists then you'd have to run daily batch(es) to add people to a list. Then clear all the existing people in that list (or you could run that clearing run the night before, getting the order right is the point). Then extract that List. 

 

Or, given the small number of leads, you could use the Lead by Filter Type export (as in fact Bulk Extract is quite complex to reliably automate, if you're not familiar with building that kind of logic).  Copy your Date field to a String field whenever it changes, then that "stamped" date-like String can now be used as a filter.

 

 

 

Alternately you could copy 

1 reply

SanfordWhiteman
SanfordWhitemanAccepted solution
New Participant
February 18, 2020

Depending on your subscription, you may not be able to use only Bulk Extract for this particular scenario.

 

If your BE supports Smart Lists then run an extract on a corresponding SL.

 

If your BE only supports Static Lists then you'd have to run daily batch(es) to add people to a list. Then clear all the existing people in that list (or you could run that clearing run the night before, getting the order right is the point). Then extract that List. 

 

Or, given the small number of leads, you could use the Lead by Filter Type export (as in fact Bulk Extract is quite complex to reliably automate, if you're not familiar with building that kind of logic).  Copy your Date field to a String field whenever it changes, then that "stamped" date-like String can now be used as a filter.

 

 

 

Alternately you could copy 

    Jo_Pitts1
    Jo_Pitts1Author
    Community Manager
    February 18, 2020

    Sanford,

    I'm pretty relaxed about the logic of BE.. namely creating a job, polling for status, downloading file when ready.

    That being said, if the Lead by Filter Type export is easier then I'm all for it :). 

     

    Do you mean this?

    GET /rest/v1/{type}.json?filterType={field to query}&filterValues={comma-separated list of possible values}

     

    Why does the field need to be copied to a string?  Does the lead query not takes dates in the comma separated list?

     

    SanfordWhiteman
    SanfordWhiteman
    New Participant
    February 18, 2020

    I’m pretty relaxed about the logic of BE.. namely creating a job, polling for status, downloading file when ready.

    Manually, sure. But there are numerous exceptions and retry handlers that you have to code for when automating it. Enqueuing can fail, and status checks need to be on a timer. The download can be interrupted and need to be Range-resumed. Etc.

     

    Does the lead query not takes dates in the comma separated list?

    Right, Date fields are not indexed as lookup fields.

      Terms & ConditionsAccessibility statement

      Loading footer...