What Do You Do?

Featured

I hate this question. It’s a terrible question.  It’s the first question at every party, but it’s still a very bad question.  Yet some law of human nature states any two strangers standing next to each other long enough will eventually ask – whether for reasons of genuine curiosity or to evaluate relative status –  “What do you do?”

“I am a Dynamics 365 Finance and Operations Functional Consultant.”

I’m met with a blank stare.  I’ve said words and communicated nothing.  Only once have these particular words been sufficient. I was on a plane.  The woman next to me lit up, “Oh! I’m a Dynamics 365 Finance and Operations Functional Consultant too!”.  Then she paused, “How do you explain what you do to your family?”

Continue reading

A Word Calculator…What is GPT?

How Will It Improve the World? How Will It Not?


a humanoid computer from the 90’s playing chess digital art, DALL-E

When I was 12, my dad took some math classes at our community college.  The advanced material required a new calculator.  One night, he pulled me over to his homework table, “Look at this!”  He typed an equation, pressed enter, and a squiggly line darted from left to right like when the patient gets zapped back to life in an ER episode.  “Ba-Bump, Ba-Bump” my dad made sound effects as the line bent up and down across the screen.

At the time, I didn’t know what a sine curve was.  It didn’t matter.  The output was exciting.

Y=sin(x^2) …exciting was relative, pre-YoutTube

Years later, I took those same classes. The green LCD screens had become as ubiquitous in math as pencils.  Now I know what sine is.  I still don’t know how a TI-83 calculates it.  What happens in that black box when you press enter?  

If cryptocurrency was the conversation of 2022 (is that finally behind us?), GPT will be the conversation of 2023.  Perhaps you need a primer: GPT is a calculator for text.

What is GPT?

GPT (short for “Generative Pre-training Transformer”) is a type of artificial intelligence model that is used for natural language processing tasks such as language translation, summarization, question answering, and language generation. It is a deep learning model that was developed by OpenAI, and it is designed to be able to learn and generate human-like text.

I didn’t write that.  I typed “What is GPT?” into OpenAI’s ChatGPT, and it generated that output. 

Continue reading

Hotkey Highlights (and Happy Holidays) – Tips for Efficient Journal Entry

Not sure what to get that special D365 accountant in your life? Waited till the last minute and now you don’t have time to buy a gift? Show your appreciation by passing along this list of journal entry tips. Better yet, send a link to our whole series on shortcuts.

#D365Blogs: the gifts that keeps on giving – even during the holidays.

These example screenshots will be from the general ledger journal form, but are applicable across journal types in D365.

Alt+N – Create a new journal

Alt+N can create a new journal header, and also create new journal lines once the journal lines are opened.

Continue reading

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.