James Edward Grant, screenwriter

 James Edward Grant

To see a video sample of scripts with production stills see the links below

Focus on Flow - Using the IN Operator to reduce SOQL Calls

Using the In Operator to reduce SOQL calls

 Skill level: Advanced

Salesforce recently added the IN and NOT IN Operators to the Flow Get Records element, this allows us to use a text array to get a series of records. An array is a group of things that are alike: a group of Contact records or a list of record ids.

This article is going to show you how to use the IN Operator to get records using a text array that you build dynamically.  For example, let’s say you have a Custom Junction object that looks up to the Contact record and the Product record. And that you need to create Opportunities for all contacts associated with Enterprise accounts with a list of Opportunity Products for each product in that Contact Product Related list. You can set up a flow, use Get Records to get the Contact Products associated with the Contact and loop through them adding the Product Id to a text variable that allows multiple values. That variable contains the list of Product Ids and you use that variable to get all the Product records. If you didn’t use the IN Operator you would have to put a Get Records in the middle of your loop! This increases the SOQL queries that you are using and decreases your flow efficiency.

AVOID THIS: Don’t do lookups or updates in the middle of your loop!

Scenario: 

Scott’s Bicycles sells bicycle accessories. Their customer service department can add products to a case - either replacement products or up-sell products. When they close the case the Sales department wants to automatically create an Opportunity with Opportunity Product Line Items. The problem is that Opportunity Products require a Price Book Entry Id and the Case Product doesn’t have that Id. You would have to do a lookup to the PriceBookEntry table with the PriceBook Id and the Product2Id - in the middle of your loop. Here’s how to avoid that.

Flow Set Up:

Create a Case Product object with lookups to the Product and the Case. Other fields you will need are Quantity and Price. Make sure you have a case record to test with and that the products you use have an entry in the Standard Price Book (we need the PriceBookEntryId!)

Add a Case Lookup to your Opportunity so you can easily navigate back to the Case. Add an Opportunity related list to your Case page layout.

Overview of Flow:

We will set up a Record Triggered Flow that will use Case data to populate the Opportunity data. Then we will get all the Case Product records and loop through the Case Products to create our list of Product Ids. That list of Product Ids will get our PriceBookEntry records. We will then loop through the Products again in an OUTER LOOP and match the ProductId with the PriceBookProductId in an INNER LOOP to get the PriceBookEntryId. Then we create our Opportunity Product Object with all our data and add that to our Opportunity Product Object Collection. When we are done with the Outer Loop we create the Opportunity Product records.

Start of Flow:

1. Create a record Triggered Flow that runs on the Case when the status equals “Closed.”

2. Create a text variable named vOpportunityId.

3. Create an Opportunity with the following map:

1. AccountId = Case Account (in my example we’ve renamed Account to Household)

2. CloseDate = Flow Current Date

3. Name = Case Subject

4. StageName = Closed Won

5. Case = Case.Id

6. Click Manually Assign Variables and select the Opportunity Id from your Create Opportunity element.

Get Your Case Products and Create your Product Text Array

Now we will get our Case Products using the Get Records element and Loop through them to grab the ProductIds in the Case Product records and put them in our text array. A lot of people find it easy to use the word List in their variable names to indicate if the variable is an array.. Once we have our text array of ProductIds we use the array to get our Price Book Entry Ids.

1. Drag a Get Records element to your canvas.

2. Get Case Products records where the Case–c field is equal to our record’s Case Id

3. Sort by Id in Ascending Order

4. Store All Records

5. Chose fields and let Salesforce do the rest

6. Store the ID, Product__c, Price__c and Quantity fields

8. Create a variable called vListProductIds of a text data type type and click the Allow Multiple Values (collection) button

9. Create a variable called ListPriceBookEntryIds of a record data type, object = Price Book Entry and click the Allow Multiple Values (collection) button

10. Drag a Loop element onto the canvas and have it loop through your Get Case Products records

11. For each item in the list - create an Assignment element that adds the Product__c field from the Current Item in the loop to the vListProductIds variable you created earlier.

12. Save and test your Flow. Press the Debug button. Your variable should look like this: {!vListProductIds} = "[01t5e000004OIdwAAG,01t5e000004OIe0AAG,01t5e000004OIdvAAG]"

The text array starts with double quotes and a bracket and each id is separated by a comma and then ends with a bracket and double quotes. {!vListProductIds} = "[01t5e000004OIdwAAG,01t5e000004OIe0AAG,01t5e000004OIdvAAG]"  If it doesn’t look like this then you didn’t click the Allow Multiple Values checkbox. 

Now that we have our vListProductIds list we can use that list to get our pricebook entries.

8. Create a Get Records element called GetPriceBookEntryIds

9. Object = Price Book Entry 

10. Conditions are Pricebook2Id = your standard pricebook Id and Product2Id IN vListProductIds

11. How Many Records to Store = ALL Records

12. How to Store Record Date = Choose Fields and Assign Variables

13. Create a Record Collection variable called ListPriceBookEntryIds 

14. Store the ID field and the Product2Id

Your Get Records should look like this:

Create Counters 

We now have two record collections - the Case Product Collection and the Price Book Entry Collection. The Case Product Collection will create the OUTER LOOP. The Price Book Entry Collection will create the INNER LOOP. We will start with a case product in our Outer loop, then we will loop through our pricebook entries and see if the pricebook entry product id matches the case product id. If it doesn’t match we check the next pricebook entry in the inner loop. When it matches we use the values in the case product and the matching price book entry id to create an Opportunity Product record which is then added to an Opportunity Product Collection variable. We need to set up some counters to count where we are in the Inner and Outer loops. Let’s set up some variables.

1. Create a number variable called vTotalNumberofProducts with 0 decimal places and 0 as a default value

2. Create a number variable called vOuterLoopCount with 0 decimal places and 0 as a default value

3. Create a number variable called vInnerLoopCount with 0 decimal places and 0 as a default value

4. Create a single record variable called vSingleOppProduct where Object = Opportunity Product

5. Create a collection record variable called vListOppProduct where Object = Opportunity Product and the Allow Multiple Values checkbox is checked.

6. Create a single text variable called vPBookEntryId

7. Create a single text variable called vPPBProduct2Id

Create Outer Loop and Inner Loop

1. Drag an Assignment element onto your canvas and call it Get Product Count

2. Assign vTotalNumberofProducts to the Count of your GetPriceBookEntryIds 

3. Drag a Loop element onto your canvas and call it Outer Product Loop and in the description write this Outer Loop loops through the Case Product and assigns variables to the Opportunity Product object variable

4. Add the Get Case Products collection to the Collection Variable field.

5. Drag an Assignment element onto your canvas and call it Loop Counter Assignment

6. Set vOuterLoopCount to Add 1

7. Set vInnerLoopCount to 0

The reason you are setting the vOuterLoopCount to Add 1 is so that we know where we are in the Outer Loop, the vInnerLoopCount is reset to 0 at the start of each Outer loop.

8. Drag a Loop element onto your canvas and call it Inner Price Book Entry and in the description write This loop goes through the price book entries and compares the product ids.

9. Add the ListPriceBookEntryIds collection to the Collection Variable field.

10. Drag an Assignment element to your canvas and call it PriceBookEntryVariables

11. Assign the current item from your Inner PriceBook PriceBookEntry Id equal vPBookEntryId variable.

12. Assign the current item from your Inner Price Book Product2Id equal  vPBProduct2Id variable.

13. Assign your vInnerLoopCount ADD 1

Now we have our first case product record in the outer loop and our first price book entry ini our inner loop and we have set two variables without price book entry record data. Next we compare the ProductIds in the price book record and the case product record to see if they match. If they do, we continue on the outer loop. If not, we go back to get the next price book entry in the inner loop until we find a product that matches. Our Decision element tells us where we are and what will happen after. 

The CountDecision determines where we are in the inner and outer loop and where we go for there.

UnderCountContinueOn - Where are we in the Inner loop? If vInnerLoopCount less than vTotalNumberofProducts we are going to continue on to the next decision point

EndCountProductMatches - Are we at the last item in the inner loop and if the Product2Ids match then we need to continue on to creating the record. 

EndCountNoMatch - if we are in the last item in the OUTER LOOP and the last item in the INNER LOOP we are done! And need to create our Opportunity Products

1. Drag a Decision element onto your canvas called CountDecision

2. Name the first UnderCountContinueOn and set the condition to vInnerLoopCount less than vTotalNumberofProducts

3. Name the second EndCountProductMatches set conditions to  vOuterLoopCount equals vTotalNumberofProducts and Current Item from Outer Loop Products__c Id =  vPBProduct2Id

4. Name the third EndCountNoMatch and set the conditions to vInnerLoopCount = EndCountNoMatch, vOuterLoopCount = EndCountNoMatch and Current Item from Outer Loop Products__c Id Not Equal to  vPBProduct2Id

5. 
   

CountDecision should look like this:

6. Drag a Decision element onto your canvas called DoTheProductsMatch? 

7. Name the first decision The Products Match and set the conditions to Current Item from Outer Loop Products__c Id =  vPBProduct2Id

8. Name the Default Outcome to Get Another Price Book Record

DoTheProducts Match should look like this:

If the products match, the next step is to create an Opportunity Product SObject variable with the info from our Case Product and then assign that Opportunity Product to the List of Opportunity Products Collection variable.

9. Drag an Assignment element onto the canvas and name it CreateOppProduct, set the fields as follows:

1. vSingleOppProduct.OpportunityId = vOpportunityId

2. vSingleOppProduct.Product2Id = vPBProduct2Id 

3. vSingleOppProduct.PriceBookEntryId = vPBookEntryId

4. vSingleOppProduct.Quantity = Current Item from Outer_Product_Loop.Quantity

5. vSingleOppProduct.Quantity = Current Item from Outer_Product_Loop.Price

10. Drag an Assignment element onto the canvas and name it AddOppProdtoList and ADD vSingleOppProduct to vListOppProduct

11. Drag a Create Records element onto the canvas and call it CreateOppProducts, click the multiple records radio button and set the Record Collection field to our vListOppProduct collection variable.

Connect the Elements and Test

We’re almost done. Now let’s connect our elements correctly. This first bit is easy.

• CreateOpportunity

• Get Case Products

• Loop - for each Item 

• Assignment Create ListProductIds

• After Last Item - connect to GetPriceBookEntryIds

The next bit, with the inner and outer loops and different decision points is more complex.

• From 5 GetPriceBookEntryIds link to 6. Assignment Get Product Count

• Get Product Bount links to 7 Outer Product Loop

• From 7 Outer Product Loop - for each item - link to 8. Assignment Loop Counter Assignment

• From 7 Outer Product Loop - After Last Item - link to 15. Create Records CreateOppProducts

• From 8 Loop Counter Assignment link to 9 Inner Price Book Entry Loop

• From 9 Inner Price Book Entry Loop - For Each Item - Link to 11 CountDecision

• From 11 CountDecision - 

1. Link UnderCountContinueOn to 12 Do the ProductsMatch?

2. Link EndCountProductMatch to 13 Create Opp Product Assignment

3. Link EndCountNoMatch to 15 CreateOppProducts

• From 12 DotheProductsMatch Decision 

1. Link The Products Match to 13 CreateOppProduct

2. Link Get Another Price Book Entry to 9 Inner Price Book Entry Loop 

• Link 13 CreateOppProduct to 14 AddOppProdtoList

• Link 14 AddOppProdtoList to 7 Outer Product Loop

That’s it, we’re ready to save and test!

So it's been 20 years

September 11 always bums me out and now it's been 20 years. Wow. 

I was at work on the 8th floor server room, in the 42nd street office of MSO Living and it was early in the morning. I had picked up a coffee (black) and a lemon poppy seed muffin from the Mezze place on 44th. The beloved C called me and said, Hey WNYC (the New York City NPR station) says a plane just went into the trade center. Apparently, they could see the trade center buildings from their conference room. 

 So, I went up to the 25th floor where the PR women had a TV because they always monitored the shows and I said, “Hey it looks like a plane went into the trade center.” We tuned into the local news and people were calling in and saying that a plane was flying really low. We could see the towers on the TV screen and we could see the towers from the window. Was a thin stream of smoke already starting? Probably, I can't be sure now. And as we watched the screen and the window the second plane hit. And Ruth called one of her friends in the CIA and said "It's terrorism, Islamic." And remembering when Timothy McVeigh had blown up the building in Oklahoma City and how everyone thought it was middle eastern terrorists at first - "I said how do we know, it's just happened." And that's when Ruth said her friend was at the CIA. 

 It was Tuesday, the sky was bright blue. I was wearing red keds and red and white checked capris and a white linen shirt. Later, I remembered feeling happy that I hadn't worn heels. 

Early in the morning the internet and phones still worked. It took about a half hour for all of that to become intermittent and then fail. 

 It was Tuesday and that meant that it was payroll day and I thought well, I'll help Luz get payroll in because the last thing we need right now is to not have money. Because we were media and publishing, a lot of the people who worked there were working for experience rather than money and rent is expensive in New York, which is to say that you could hire talent for cheap and they would hope to parley that into a more lucrative career. We had a dedicated modem for payroll transmission. The phone line failed. After about 20 minutes of trying, I said, let's try the internet method - since we still had internet at that time. I had been testing the internet method of transmission so I re-jiggered the setting and pointed our test environment to our production environment and then the internet failed. I had a challenge now. I told Luz that we would make a disk copy, so I got a disk and put the payroll files on there. Luz had somehow got through to the payroll service and they were uptown and we could drop off the disk there. 

Luz and Tom and I walked out of the office towards Grand Central Station. There were tons of people. And we were standing on 5th Avenue in the middle of the street with a direct eye-line towards the towers when they collapsed. Was everyone silent? Did everyone scream? I don't remember, I remember the horrible feeling in the pit of my stomach. Afterwards, Luz - a total New Yorker - grabbed the last taxi on earth and she and Tom took off. Payroll was delivered. Luz and Tom got home. 

 Back at the office, someone had ordered Pizza and no one knew what to do. Around 2:00 the beloved C called(!) and the plan was for me to walk to the lower east side to our brother's house. So, I set off - again thanking the stars that I had not worn heels to work. 

There was an eerie silence, there were no planes in the sky. Not a lot of traffic. It was hard to walk towards the burning towers, everything in me said I was going the Wrong Way. A truck back-fired and I dropped to the sidewalk and rolled against the side of the building. Yes, it was an over-reaction. But I'd never been in a bombed city before. 

I got to the lower east side. I bought a pack of cigarettes. I started smoking again after 6 months of not smoking. I stayed with E and V for a while and then we noticed the F train had started to run again. I got on the F train. I got home. Chris said he just walked to the east river and boats were taking people across. 

 The towers burned for days it seemed. I don't remember how many days. We were in Brooklyn and had been able to see the towers from our stoop. One summer evening we had been on the roof of the building, drinking wine and there was low cloud cover. We watched and I noticed that there were lights, randomly appearing in the clouds. I said "Hey are those aliens?" Soon enough we realized that the clouds were moving and parting and the lights were the lights of the offices in the twin towers. 

It seemed the towers burned for days and we were in the direct line of the smoke. You could walk outside and see bits of spreadsheets, parts of photos, pieces of resumes and memos floating down from the sky; the smell of smoke was pervasive. I thought, I'm breathing in the ash of people who worked in the buildings. 

 The subways were quiet when people started going back to work again. People were jumpy. Empty packages left on the subway would get reported, the trains would stop, delays were common. I was on a subway and this big guy had a long, thin bag - something that looked like a rifle carrier, and he got out a balaclava and put it over his head so you couldn't see his face and I got out at the next stop. 

 And then - everyone forgets about this. But the anthrax thing happened - our local post office at work was closed. People reported that their magazines had "white powder" in them. And the FBI had a small footprint in the Starret Lehigh building where we worked. They basically had parking there. The FBI moved into the building since their building downtown had been destroyed and we were suddenly working in a "protected" area - army soldiers with guns set up barricades and patrolled. The cooking editors baked a lot of fancy cookies for them. 

But I never wanted to go to war. I didn't think that going to war was a good thing. I didn't understand why we were invading Iraq - a country that had nothing to do with the attacks. I went around saying "But Sadam Hussein is a pan Arab nationalist and not an Islamic extremist - they don't get along. This makes no sense." It's why I didn't want to vote for Hillary Clinton she was such a hawk on the war and we wanted to grieve.

Cicadas, they've been around way too long. They were fun. Brood X was interesting. Great bugs. Wish them well. Wish them gone now.

Tango inspects the Patio Project

Here I am

Here is some text

Embedded Video Conference