Win+V: Super Cut & Paste

“Manual data entry builds character,”
Joel Leichty
“Character gives us qualities, but it is in our actions–what we do–that we are happy…or the reverse,”
Aristotle

If you need to paste multiple values into multiple places, WIN+V should be your new best friend. Where regular CTRL+C copies something and CTRL+V pastes that last thing you copied, WIN+V allows you to select from your history if previous items copied.

Continue reading

Navigating ERP Environments: DEV, TEST, & PROD

Multiple environments are necessary during ERP implementations. An ERP implementation usually involve at least three environments, though additional environments might be added depending on implementation project requirements. DEV, TEST, and PROD, are the most common names for these three environments

PROD (May also be called GOLD)

After implementation, PROD refers to the live ERP environment. Prior to implementation, this is a transaction-less environment where tested configurations are setup. Transaction-less because ERP systems don’t have a “delete posted transactions” button, so you want to keep this environment clean from test transactions. So where does transaction testing happen?

TEST (May also be called UAT for User Acceptance Testing)

During the implementation, subject matter experts and end-users will setup sample configurations and model business processes with test transactions in the TEST environment. The desired, approved configurations will then be setup in the PROD (GOLD) environment.

DEV (Mostly just called DEV)

DEV environments are necessary as ERP projects frequently require customizations. A developer can build and deploy his developments to the DEV environment without interrupting the flow of user testing. Once any technical bugs are smoothed out, the customized code from the DEV environment can be promoted to the TEST environment, where it will be validated/approved by subject matter experts or other functional users.

Continue reading

Why Does Daddy Have to Work?

Working from home with kids is full of distractions. 

Lawrence has been walking for a couple months now. He likes banging on my office door a few times per day. Very determined for his age, he won’t stop until I open the door. When I do, his little grunts turn into a big smile.  He takes me by the hand to lead me into the living room, or into the kitchen, or just back-and-forth to nowhere in particular. If I leave my office door open in the evening, Lawrence likes to slip in and press a button on my keyboard to flash on the sleeping monitors.

The other night, while wrestling Calvin into bed, he hopped up into my office chair and started hacking away at my keyboard, “I’m working Daddy!” He even put on my headset and let me take a picture. I have to lock the office door while I work because, unlike Lawrence, Calvin can reach the handle and feels at liberty to barge in anytime he wants to play.

In April, I left a salaried job to start freelancing.  One reason for the change was to work on my own terms, to spend more time with the kids. More time with the kids isn’t all time with the kids though.  There are still trade-offs. The freelancing still needs to be done. It’s hard to say “No, Daddy can’t play right now. Daddy has to work,” but it’s necessary at times.

Calvin and I have this script: I ask “Why does Daddy have to work?” and Calvin responds, “To make money.” I ask “Why does Daddy have to make money,” and Calvin responds, “To buy Calvin toys!” 

This is a partial and inaccurate truth. I’m not sure how to best correct it.

Continue reading

Data Violates Integrity Constraints – Data Management in D365

Failure is no fun.

“Data Violates Integrity Constraints” is an error I encounter too often. If you get this error, perhaps this post will help you push past your failure.

In the scenario below, you try to import excel data through a data entity, but get a failure error.

That’s fine you say, I’m a professional. So you click view execution log to troubleshoot the issue.

If you try to view the staging data, you’ll see no staging data has loaded.
If you click on Staging log details, you’ll get a curious error message:
The data value violates integrity constraints.

This is curious, because it seems you haven’t loaded any data at all, let alone some data that would have triggered a validation error. You re-check your excel file, and the data you wanted to load seems correct.

So What’s The Problem? How do you fix it?

Frequently, the issue is there are some “blank” rows at the bottom of your excel data that are throwing off the upload. Data Management is trying to prevent you from importing those blank rows. Highlight the row below your data, press CTRL+SHIFT+DOWN and delete all the rows below your data. To be safe, you can do the same thing to the far right to clean up any columns that may be causing trouble as well.

Then, just like wherever else we encounter failure, set it up and try, try again.

Good luck!

Power Query Demo – Connecting D365 Tables in Excel

A D365 Finance and Operations reporting issue I had recently:
How do I report on all published WBS activities by their related projects?

Well, the activities table (smmactivities) doesn’t have a project id field.
If you are here for that specific question, the necessary table relationships are:

  • smmActivities
  • HierarchyTreeTable
  • HierarchyLinkTable
  • ProjTable

But I was interested in the process of doing this more than the specific reporting task. A basic way to do this could be to dump those tables into excel, then use a bunch of vlookups to add the associated project data to the activity rows.

Nothing wrong with that, but the final product would be a bit clunky…the whole smmActivities table, with a few extra columns. You could then delete or hide the unnecessary columns, but if you need to create the report again you’ll have to redo those same operations. If you need to add some attribute, you’ll have to do more vlookups (or xlookups, or index matches, whatever your preferred Excel operation). Is there a better way?

Introducing Power Query

Power Query is a tool to get and transform data in excel. In the example below, I exported the 4 desired tables, pointed to them as data sources, joined them together in Power Query, and then used Power Query to clean up my reporting output.

Here’s a video of that effort:

Reasons to Use Power Query

A Better Way to Get Data
Power Query can connect to other tables in your excel file (like in my example above). Power Query can also be used to connect to other external data sources. In a more sophisticated reporting example, there is probably a way I could connect directly to a D365 data entity. I’ll look into that more in the future.

A Better Way to Transform Data
I found Power Query to be a really pleasant experience for transforming data. A frequent D365 data migration issue is you’re given some legacy file, with some legacy file headers, which needs to be formatted into a D365 structure of columns and headers.

Instead of hacking away at the source data, which would be my first excel instinct. Power Query lets you add columns or rename headers programmatically…making it simple to redo the changes if you later get a NEW file of legacy data that also needs to be transformed.

I particularly like that Power Query shows a list of “applied steps”, which makes it easy to follow the transformations that take place between the original and transformed data.

Power Query shows applied steps in the process of transforming data

My first-time user experience is that Power Query is a great tool, and I can see it becoming an integral part of improving D365 data migration projects.

Additional Resources

My D365 Excel Add-in Disappeared – What do I Do?

The excel add-in connection is one of my favorite Dynamics features. What brings me more joy than updating a bunch of records through the excel add-in, or using the template to load a journal entry with tons of lines? Well, maybe hearing my son say “I love you too” brings me slightly more joy, but the excel add-in thing is a close second.

That said, sometimes I unintentionally close the excel add-in. Frustrated, I used to redownload my desired template from D365 and redo my work. This isn’t necessary though!

Here’s how you can get the excel add-in back:

Does anyone else frequently close this before they mean to?

  1. Click Insert
  2. Click My Add-ins
  3. Click Microsoft Dynamics Add-in
  4. Click Add

The Time We Took Calvin To The Hospital

The park.

A new parenting first: we took Calvin to the hospital today.  When he was born three years ago, we took Calvin from the hospital.  A nurse inspected the snugness of our rear-facing car seat, qualifying us to take a newborn home without any extra supervision.  It felt like there should be more qualifications for taking a newborn home.  We didn’t really know what we were doing.  Does any new parent?  

Twenty-some months later, we performed the ritual again taking his brother Lawrence home from the hospital.  We had a slightly better idea what we were doing.  All the car seat straps were in place before the nurse even reached our car. 

And then there was today.

Luana called me outside, voice full of urgency, reminding me of the urgency in those previous hospital trips.  Something was wrong.

She was carrying Calvin.  Calvin was crying.  

“He fell…at the playground.  He crossed the bridge. He turned and lost his footing. He fell.”

I was in shock.
“The wobbly bridge or the snake bridge?”  I asked.

Luana’s expression changed from distressed to confused.

I continued, “The wobbly bridge is on the right.  It shakes when Calvin crosses and he goes ‘WO-BBB-LY BRIDGE!!!’  The snake bridge is on the left. I stick my arms through the cargo netting and he shouts ‘Snakes!!!’ as he passes”

It was Luana’s turn to be shocked.
“Does it matter?” she asked.

And she’s right.  It doesn’t matter.  What matters is that Calvin fell.  What matters is that a five-foot fall is a long fall for a 2-and-a-half-foot body.  What matters is that falling on brick is a hard fall for a body of any size.

“He fell on his arm,” she adds.

I gather Calvin’s crying body in my arms.  A golf-ball lump of swelling bulges at his elbow.  The arm is folded and doesn’t want to unfold.  Calvin’s crying turns into screaming if I touch him anywhere below the shoulder.

Once, when I was 13, I flew over the nose of my skateboard and landed on my elbow.   Gathering myself, I realized my left arm wouldn’t unbend.  Holding the left arm with my right, I walked home and asked my mom to take me to the emergency room.  The arm broke just above the elbow.  Surgery followed, then a cast, then rehab.  Two decades later, the arm has its full range of motion again, but the surgery scar on my elbow still makes a happy face if I curl the bicep.

That was the last time I skateboarded.  Will Calvin ever skateboard?   

I hold Calvin and he sobs. I don’t know what to do, but I’m trying to be calm because I don’t want Luana to think I don’t know what to do.  

What can you do?  You can’t make him unfall.
I pray.

I pray, and I kiss Calvin’s head, and I tell him it will be ok.
Will it be ok?

Is the arm broken?  We need x-rays. We call a cab to the hospital and pile into the backseat when it arrives.  Calvin falls asleep in my arms.  At least he’s not crying anymore.

This is a good time to mention we’re in Brazil.  We’ve been visiting Luana’s hometown for a few months.  That’s why we’re in the back seat of a car without any carseats.  I can feel the American hospital nurse from 3 years ago scowl disapprovingly into the future.  Are we still qualified to be parents?

Brazil is also why the playground is built on brick instead of sand or mulch or that rubbery-tire stuff American playgrounds are built on.  Would Calvin’s arm be ok if he had fallen in an American park?

We arrive at the hospital.  It’s a white rectangle building with a church on one side: Hospital de Clínicas Nossa Senhora da Conceição.

The hospital.

Calvin still asleep in my arms, we walk into an empty reception area.  Luana knows the woman at the front desk.  This isn’t surprising.  Luana’s hometown is small and Luana used to be a nurse. Luana knows almost everyone at the hospital.

The receptionist asks if we would like to pay to see a private doctor, or use public health.  The question is disorienting.  American hospitals never asked me that.  I want the best medical care possible for my son.  What’s the difference?  

They’re the same, the receptionist explains, which leaves me more confused.

Luana helps move the decision along.  We’ll use public health.  The receptionist guides us back out the main hospital entrance, around the building, to a side entrance of the hospital.  The side entrance is marked “public health”.    

In a new empty reception area, we’re greeted by new receptionist friends who check us in.

Quick wait.  Quick triage. Quick consult.

Everything in the hospital is very professional.  Everyone’s uniform is pristine.  The nurses and doctors smile when they see us. Their faces tighten sympathetically when they peer at Calvin sleeping in my arms and we explain what happened.  They nod understandingly.  X-rays are prescribed.

We sit in a waiting area while a technician prepares the x-ray room.  Down the hall, I can see the reception area – the one we first entered, at the front of the hospital. It dawns on me how public and private healthcare can be the same.  The difference is the door you enter.  

The x-ray room is ready. Calvin wakes up as we enter.  

It must be very alarming to be 3 years-old, fall asleep in the backseat of a car, and then wake up in an x-ray room for the first time. Especially when everyone in that x-ray room is wearing masks to prevent the spread of an invisible disease.  Especially after you’ve fallen on your arm.

Calvin does not handle waking up well.  He screams and cries again. I can’t tell if it’s more because of his elbow or our new location.  I hold him in my lap, try placing his arm on the x-ray plate to take the image.  He is having none of it and refuses to sit still.  I try to calm him down.

“No need to be afraid.  This is a big camera, just like your toy camera at home.  Can we take a picture of your arm?”

“NO PICTURE.  NO PICTURE! NO PICTURE!!!”  

Calvin sobs and wiggles off my lap.  He bolts for the door.

“OUT. OUT! OUT!!!” Calvin cries.

Calvin tries jiggling the door handle.  He tries yanking it with all his little might.  The heavy x-ray room door won’t budge.  Finally, he hangs from the door handle and tries kicking off the wall.      

The x-ray technician and I look at each other.  Calvin is hanging from the door handle – supporting his whole weight – with the arm we are concerned might be broken.  X-rays won’t be necessary.  Calvin will be fine.  Maybe he just needed some time and a nap.

Is this answered prayer?  Is this our miracle?
Is this the first patient cured by x-ray machine?

I think so. I don’t know.

Luana and I hug Calvin and comfort him and calm him down as we leave the hospital. 
We call a cab to go home.

Maybe we are qualified to be parents.

Brazilian Kite Culture

Boy with a kite on our street in Brazil

The sun descends.  The winds pick-up.  Colored shapes rise above the rooftops.

Each shape has a name: The squares are raias.  The pentagons are piões.  The diamonds are pipas or papagaios, depending on their size.  Tie a ribbon tail to any shape and it becomes a rabiola.  In English, we just call them all kites.

“Kites look like they’re for kids, but they’re for adults.”

My neighbor, 30 years old, tells me this with great confidence as he tosses up his papagaio.  A set of quick jerks on the line and the kite is 50 meters above us, coasting in the breeze.

“The kite follows its nose”, he explains, “pull the line, and it moves in that direction.  Give it slack, and it will spin.”

He hands me the line and lets me give a few tugs.  There’s an art to pulling the kite-string for lift, then quickly releasing to let line out for the kite to sail higher.  Most kids do this with their bare hands. Those with money, or most adults, use geared wheels for spooling kite-string. The wheels have a crank to pull and release the line with leverage.  My neighbor’s kite wheel has the Flamengo football-club banner etched into the side.  

“Have you ever seen a more beautiful flag?” he asked when he first showed it to me.

Kite-flying is the opposite of fishing.  Fishing looks down in the water.  Kite-flying looks up in the air.  Fishing pulls a swimming thing to land.  Kite-flying sends an earth-bound thing to soar.  

They do share the same mechanics: pull in when there’s slack, release when there’s tension.

A small crowd gathers around us.  Everyone flies kites here, but it’s not everyday you catch a gringo doing it.  I’m the only gringo in the neighborhood.

Down the street, another neighbor’s little red kite lifts into the air.

My neighbor takes back control of his own kite, working it across the sky.  He pushes to make the kite spin, pulling hard as he catches the nose parallel with the horizon.  His kite dances in bursts toward the little red one.  Another push/pull and his kite rises up above the little red one.  One more and it darts down, down.  My neighbor shakes the line with the ferocity of a graffiti artist warming up a can of spray paint.

The little red kite thrashes.  It tries to climb, realizes it cannot. Its line is pinned under my neighbor’s line.  Tension builds.  Then suddenly, there’s no tension at all.  The little red kite spins free, severed from its line. It lilts in the wind a little more, drifting out of the neighborhood, descending beyond our view.

My neighbor has cut down the other neighbor’s kite.  The little crowd around us cheers.

“Kites look like they’re for kids, but they’re for adults,” He reminds me.

In Brazil, kite fighting is part of kite flying. Kite flyers buy their paper kites in bulk, anticipating multiple losses on a given day. They master fine techniques in preparing and positioning kites for battle.  Kids used to coat kite strings in glue and run the lines along broken glass to make better cutting instruments.  The modern practice is to buy string coated in aluminum oxide. The wonders of industrialization.

“Feel this – the aluminum oxide is sharp.  It can cut you”.  My neighbor explained.  

Aluminum-oxide coated string is expensive.  My neighbor attaches 50 meters of it to his kite “where the action is” so he can slice the lines of other kites.  The fortified line is then attached to 500 meters of regular kite string, spooled around his kite wheel.  550 meters: at that distance, a kite is just a dot. It’s impossible to tell its nose from its tail. I wonder about the range at which 550 meters of line can attack another flying kite.  It’s a math problem with the area of a circle, and maybe cosine, but I can’t figure out the angles and inverted cones.    

Aluminum-oxide coated string is dangerous.  Luana worries about our kids getting too close and cutting themselves on it.  Abandoned kite strings pose a community risk when they get tangled in fences or overhead lines.  My neighbor’s motorcycle has a special antennae to deflect errant kite-strings from clotheslining him while riding through the neighborhood.  WWII Jeeps were outfitted with similar apparatuses: “wire anti-decapitation devices”. 

Aluminum-oxide coated string is also illegal.  A point my neighbor emphasizes several times in the process of learning me up on Brazilian kites.

“If it’s illegal, where do you buy it?” I ask.
“Everywhere” the crowd responds.
“Like weed,” someone suggests in a tone that seems to be part statement-of-fact and part solicitation.

Kite flying and kite fighting are tethered in Brazilian culture.  Craft and technique in building and operation. Style and flair in colors and shapes.  Sidestepping inconvenient rules for advantages. Games of bravado and status as flyers risk their own kites to cut down those of others.  Beauty, Joy, Daring, all tied in one hobby.  

Kites look like they’re for kids, but they’re for adults.

Outlook Tip – Calendar Timesheet Entry: Dismiss Reminders For Past Events

Outlook Calendar Timesheet Entry

After years of struggling with timely timesheet entry, I finally found a time entry process that works for me.

(Hello all my former managers! Thanks for subscribing!)

Throughout the day, I enter my time as Outlook events, using a different Outlook Calendar for each customer. At the end of the week, I then transfer those entries into my actual timekeeping system.

My Outlook Calendar ends up looking something like this

Is this the most efficient way to enter time? Well, probably not.

But it does have some advantages:
1) My meetings are already on my Outlook calendar.
2) I’m in Outlook throughout the day, so the friction of switching applications is reduced.

Don’t let Perfect be the enemy of Good. Find a process/system that works for you.

Automatically Dismiss Reminders For Past Events

One issue I had with this system is that entering a past time-tracking event into my Outlook calendar, would trigger a reminder message. But I don’t want a reminder notification…the event entry itself is the reminder for my end of the week timekeeping process.

This is less than desirable. Fortunately, there’s a fix!

In Outlook, click File (top left) then goto Options (Options will be on the bottom left).
In Outlook Options, goto Advanced, and then select Automatically dismiss reminders for past calendar events.

Problem solved. No more retroactive reminder reminders😊

Why Edge Is My Preferred D365 Browser

My favorite Chrome shortcut for D365:

Ctrl+F to find menu item
Ctrl+Enter to click on found menu item

My favorite Edge shortcut for D365:

Ctrl+Shift+K to duplicate tab
(great for looking up multiple records at same time)

What I just learned in Edge for D365

Ctrl+F to find menu item (duh)
Esc, Enter to click on found menu item (ooohhh…)

Thank you for coming to my Ted Talk.