“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.
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:
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.
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.
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?
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.
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.
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.
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😊
In college, I was an econ-nerd. My heroes were Adam Smith, Friedrich Hayek, Milton Friedman, Thomas Sowell. I listened to every Russ Roberts’ EconTalk episode on my iPod Nano. Russ got to interview celebrities(!) like Richard Thaler, Sam Peltzman, and Tyler Cowen each week. Scarcity, opportunity costs, utility functions, I thought I was discovering a secret language to explain the world.
My college had two Nobel prize winning economics professors. They were kind of a big deal.
My sophomore year, I took a class with one of the Nobel prize winners: Vernon Smith. Vernon won the 2002 Nobel prize for his foundational work in experimental economics. He had the idea you could test economic theories by creating games and offering people real money to play them. When you do this, you find that humans don’t always act like the profit-maximizing utility-robots some economic theorists think they act like. This is interesting.
An example: I give you 10 dollars and ask you how much of the 10 dollars you want to share with another participant. A selfish utility-robot would keep the whole $10 for himself. Basic economic theory predicts you would keep the whole $10 for yourself. But some people do not. Some people share – even if the other participant is a stranger. How odd.
Another example: I give you 10 dollars and ask how much of the 10 dollars you want to share with the other participant. BUT IN THIS EXAMPLE the other participant can reject your offer. If they reject your offer you both get nothing. In a world of Utility-robots, Utility-robot A would want to keep as much of the money as he can and only offer $1 to Utility-robot B, which Utility-robot B would accept because one dollar is more than zero dollars. However, Human B often rejects these proposals. Humans are not utility-robots. Humans are more complicated. Weird.
So Vernon Smith set up a lot of these games, and he gave out a lot of money for people to play them, and recorded the results, and then he got the Nobel prize. He was basically MrBeast before there was a MrBeast. Imagine how famous Vernon Smith would be if YouTube had been bigger at the time.
And when I was a sophomore, I took Vernon’s class, and we got to play the games and understand the ideas behind them, and it was pretty awesome, and taught me the valuable lesson that humans are not robots.
My college’s other Nobel prize winner was more elusive.
James Buchanan didn’t teach any classes, but he was still a very big deal. Buchanan won the Nobel Prize in 1986 (the year I was born) for his “contributions to the theory of political decision-making and public economics.” He’s one of the founders of Public Choice economics, which applies economic thinking to political decision making.
One public choice idea is “Instead of imagining politicians as benevolent public servants, what happens if we imagine them as rational actors responding to incentives?”
In other words, Buchanan thought “If politicians were utility-robots, what would they do?” And to some people’s surprise, theorizing politicians are selfish rational actors is a pretty good way to model how they act.
To recap: people are more complicated than utility-robots, but thinking of politicians as utility-robots can be a useful way to predict their behavior.
Another fun fact: James Buchanan lived in the James Buchanan House making him (as far as I can tell) the only professor at my school living on campus in a house named after himself.
So the summer after my sophomore year, I learned there was an economics seminar on campus, headlined by Deirdre McCloskey and Nobel Prize Winner James Buchanan (with reception to follow at the Buchanan House). Economics seminars are one way academic economists share their works in progress. They read their draft papers to each other, offer feedback, and think about how their ideas fit into the web of all the other ideas other people have thought about. Excited about the opportunity to meet my school’s other Nobel prize winner, I asked if I could attend and the seminar organizer graciously let me sit in.
I was the only undergraduate. The seminar was mostly professors or people aspiring to be professors. The next youngest person there was probably 30, which feels very old when you are 20, but also very young when you are 35 and reflecting on the event. The average age was probably 50 – very, very old if you are 20 – and if my math is right James Buchanan himself was about 90, unimaginably old to me even today.
Drafts papers were presented and debated. Most of the ideas were too chunky for my mushy undergraduate brain to absorb.
Then Deirdre McCloskey presented. Deirdre is not the stereotype of a lecturing economics professor. She has a stutter in public speaking that leaves you craning for the next word in her sentence. But her ideas are so crisp and clear and provocative that you are anxious for those next words. Her presentation that day has stuck with me all these years later.
A big idea in economics is gains from trade. I choose to trade with you because it makes me better off, and you also choose to trade with me because it makes you better off. Trades are positive-sum games, and voluntary trade is one of the foundations of our current prosperity.
Deirdre presented the idea that conversation is an exchange of words. Conversation is a trade of sorts, so conversation should have some positive-sum benefits, creating value like we see in traditional trade.
Economics hasn’t focused much on conversation’s benefits. One reason is the benefits are harder to add up than the dollars and cents of business activity. Deirdre also proposed another theory for this lack of attention: Economics is a male-dominated field. Men are not as naturally given to conversation, and therefore tend to overlook its value.
Then she proposed a solution: More women in Economics! She closed “this is an endeavor to which I hope to have done my part”.
Later, Professor Buchanan gave a talk. I don’t remember a word of it.
Following the seminar, there was food and drinks and mingling. I remember feeling lost. Out of place in the age gap between myself and the other seminar attendees. And then Deirdre McCloskey, headline seminar attendee, came over and talked to little-old me. Maybe she noticed I was different. Maybe she sensed I felt out of place. Whatever the reason, she engaged me in conversation and I suddenly felt seen. I felt I belonged.
Then she did the most amazing thing.
Deirdre McCloskey walked me across the room to where a circle of economists had gathered around Nobel Prize Winner James Buchanan.
“Jim,” she said, “I’d like to introduce you to a friend of mine, Josh Knox. He is an undergraduate here at George Mason University”
I was starstruck. I began to extend my hand.
Nobel Prize Winner James Buchanan grunted, “Yeah, we met already,”
But here’s the thing: we hadn’tmet already. And Deirdre knew we hadn’t met already. But also, he’s Nobel Prize Winner James Buchanan. What are you going to do?
Deirdre shot me a glance and smirked.
Maya Angeleau said “people will forget what you said, people will forget what you did, but people will never forget how you made them feel.” I smile when I remember what Deirdre McCloskey said in her draft paper, what she did in her introduction, and how she made me feel that day.
I won’t get a second chance to meet James Buchanan, he passed away in 2013. But if I get a chance to meet Deirdre McCloskey again, I’d like to thank her for her courtesy that day. I’d like to thank her for starting a conversation and making me feel valuable.
A small note for anyone who needs to hear it: If you regularly switch between accounts on the same online services, consider using different browser profiles for your desktop.
Each browser profile will save your cookies and login details separately. This will reduce the hassle of having to regularly sign out of account A to login to account B, or opening an incognito browser and manually re-signing into account B every time you need to do something there.
Some Use Cases:
Switching between Microsoft accounts in different Tenants
Switching between a standard D365 account and a test user account.
This is my favorite feature of using browser profiles. If you have browser windows open for two different profiles, you will notice that there will be two different browser icons in your. Right-click on a particular browser icon and you can pin that browser profile to your taskbar.
Now, the next time you want to work under that profile, you can get there directly from the taskbar icon.