Rose Business Solutions Dynamics GP

Author Archive

I found this tech doc (Article ID: 935619) for masking the social security field on a payroll report. It wasn’t exactly what I needed but it did the trick.

I’m posting this mostly for me so I don’t have to look too far and wide to find it again.

Step 2: Create a calculated field

1. In the Report Definition window, click Layout.
2. In the Toolbox window, click Calculated Fields, and then click New.
3. In the Name field, type SSN.
4. In the Result Type list, click String.
5. In the Expression Type area, click Calculated.
6. Click the Constants tab, and then click String in the Type list.
7. In the Constant field, type the text that you want to mask the social security number. For example, type ***-**-, or type XXX-XX-.
8. Click Add.
9. In the Operators area, click CAT.
10. Click the Functions tab.
11. Click User-Defined, and then click System in the Core list.
12. In the Function list, click RW_Substring, and then click Add.
13. Click the Fields tab.
14. In the Resources list, click Payroll Work Check.
15. In the Field list, click Social Security Number, and then click Add.
16. Set the first character of the substring. Then, set the length of the substring. When you add the Social Security Number field, use the constant integer 6 and the constant integer 4, respectively. To do this, follow these steps.
    Note You must add this information when you use the RW_Substring function.
    1. Click the Constants tab.
    2. In the Type list, click Integer.
    3. In the Constant field, type 6, and then click Add.
    4. In the Constant field, type 4, and then click Add.
       The calculated expression that you create resembles the following:

       "XXX-XX-" # FUNCTION SCRIPT( RW_Substring UPR_WORK_Check.Social Security Number 6 4 )

17. To save the calculated field, click OK.

For years I’ve used the copy company setup tool to copy over setup files from one company to another. The tool makes copying over GL accounts, vendors, customers and basic company setup such as fiscal periods and payment terms easy and painless. Pre GP 2010 this was an automated solution. GP 2010 the copy company tool is actually a free component in the Professional Service Tools Library so the PSTL must be installed to use the copy tool with GP 2010.

In the process of copying over the Financials module the checkbook is also copied which is fine except the balance of the checkbook is also copied. This fact is usually figured out after the user has been entering transactions for a period of time and goes to do the first bank reconciliation.

To fix this issue I usually update the CM00100 table with a script like the following:

UPDATE CM00100 SET CURRBLNC = ‘XXXXX’ WHERE CHEKBKID = ‘YYYYYYY’

(Replace XXXXXX’s with the correct Current Balance and YYYYYYY ‘s with the correct Checkbook ID)

To figure out the balance I either figure out what the beginning balance was and subtract that from the current balance or use the current GL balance as a guide. The key would be to make the reconciled balance tie correctly to the GL.

The last reconciled date can also be deleted if that is not wanted either.

Happy copying.

When I was a freshman in a U.S. college I hung out with a group of friends from my little hometown in Canada. We were appalled by the lack of knowledge of our American roommates (and most of our fellow student body) about our beloved homeland. On October 13th that year we decided to celebrate Canadian Halloween. We dressed up in costumes and went around trick or treating (mostly around girls housing complexes.)

There is no special Canadian Halloween (unlike Canadian Thanksgiving, Independence Day, Remembrance day, Family Day etc.) but my recollection is that most of the people we trick or treated to were quite open to our Canadian culture when we announced it was Canadian Halloween. If they didn’t have candy we said we’d take canned soup or any type of food. I don’t think we had to go shopping that week and we met a ton of girls. Although I’m sure most of the girls we met decided that they would never date a Canadian after meeting our motley crew.

What does this have to do with opening back up a bank reconciliation after it has already been posted? I’m going to discuss a method below that some of our consultants use to open back up a bank reconciliation. You can believe me without question or you can test this out first on a test company.

******************************

SQL queries:

1) Determine the Recon# of the reconciliation to be un-done (replace XXXXX with the appropriate Checkbook ID):

select * from CM20500 where CHEKBKID = ‘XXXXX’

order by RECONUM

2) Change the transactions in that recon to not reconciled (replace the ##.##### with the exact RECONUM determined in #1 above):

update CM20200 set Recond = 0 where RECONUM = ‘##.#####’

update CM20200 set ClrdAmt = 0 where RECONUM = ‘##.#####’

update CM20200 set clearedate = 0000-00-00 where RECONUM = ‘##.#####’ update CM20200 set RECONUM = 0 where RECONUM = ‘##.#####’

3) Remove the Recon from history (replace the ##.##### with the exact RECONUM determined in #1 above):

delete CM20500 where RECONUM = ‘##.#####’

4) Update the Checkbook Master with the revised Last Recon Date and Last Recon Amount (Replace MM with the desired Month and DD with desired day. Repace #######.## with the appropriate amount – no commas. Replace XXXXX with the appropriate Checkbook ID):

update CM00100 set Last_Reconciled_Date = ‘2010-MM-DD 00:00:00.000′ where CHEKBKID = ‘XXXXX’

update CM00100 set Last_Reconciled_BALANCE = ‘#######.##’ where CHEKBKID = ‘XXXXX’

**********************************

Although I’ve told anyone that asks for the past several years that you can’t unpost a bank reconciliation that’s not entirely true. The above method works great. It just takes a little SQL knowledge and you can be re-reconciling in no time.

But don’t just take my word for it. I once swindled a whole apartment complex out of all their precious chocolates and candy.

Happy Canadian Halloween.

The payroll person in our company contacted me today and asked if I’d help fix an issue. Normally I’m pretty responsive to people who call but when it’s my payroll person and today is the day we get paid…I drop everything and focus all attention to the issue at hand.

Building the checks was fine but when trying to calculate check we got the following error:

Clicking on more info we found this message:

Come to find out PC tables relate to human resources. We use HR but not to a great extent. The PC  Fund setup table (PC40200) was missing on the company database. After running a backup  I went into File>>maintenance>>SQL>>company database, HR product and highlighted the PC Fund Setup table. Then checked off create table and chose process.

When I browsed out to the Fund Account Options window it actually opened up and the payroll processed correctly.

So a couple of points here.

1. Our payroll lady knows how to get things done. I think she starts every conversation “I can’t get payroll done if you don’t help me do this.”
2. We are not using the Fund options in HR so creating the table wasn’t a big deal
3. I don’t know if there was a service pack put into the system in the past couple of weeks or not. I couldn’t find any issues documented around the blogosphere so maybe it is just us
4. I did a backup. I always do a backup
5. Everyone in our company should send me chocolate and candy because they got paid on time

I found this on a clients desktop today.

My immediate reaction was “Will this system ever die?”

I remember upgrading them from GPA to Dynamics around the GP 7.0 range. We left the GPA system up and working and I guess they’ve never gotten rid of it even through operating system updates and the various GP updates as well.

Microsoft may say the life of a system is 5 years but we all know that a good accounting system/version could be around for decades.

Sometimes I miss those DOS days….NOT.

What’s the oldest GP version you have come across in the last year?

Microsoft calls it day 3 but I’ve been at this conference for 4 days so that’s what I’m going to call it.

Had a chance to hear Sir Richard Branson of Virgin Airlines today. He was rather casual and didn’t really give a prepared message. Here are some of my notes.

1. “Screw it, let’s do it” attitude gets a lot accomplished
2. Be flexible with employee’s. Make sure they are enjoying work
3. Break ideas up into companies
4. Then find someone to run the company so you can stay focused on the next idea/big picture
5. Forgive your enemy. Call up an enemy and have lunch with them. It’s better for you and for them
6. Challenges come and setbacks will occur. Press forward with hope and belief

I really like the guest speakers at these conferences. They are usually very inspiring and Sir Richard Branson didn’t disappoint.

Had partner awards night tonight as Rose Business Solutions was a finalist for Hosting Partner of the Year with Microsoft. Walked the red carpet and took silly pictures of the Rose gang that is here.

Final day of conference is tomorrow. The ending concert is Smashmouth and Los Lobos boys. Should be a good final day.

Once again started off the day trailing the Silver Bullet. I’d have a better chance catching the wind then Steve Chapman in running shoes.

Made it to the key note and was entertained by some pretty cool stuff. The new Lync product was displayed. Looked like a great product to help with all forms of communication including chat, video conferencing, voice, meeting coordination including white board and presentation abilities. The new Windows phone coming out in the fall looks really cool. I have a Zune and it actually looks a lot like that.

Rose Business Solutions was mentioned during Karill’s keynote even if it was only a passing mention.

The booth was pretty steady today. A lot of people from around the world. Not too many dynamics folks at the conference so there is quite a different feel than Convergence.

We have some pretty cool swag. The Beach ball is great and we have shirts and visors. I can understand people wanting this stuff. I need to bring home some beach balls to the kiddies myself. We have that stuff with the intention of drawing people in to talk to us. That’s pretty well the ploy to all booths at any conference. (That or hired booth babes.)

It’s funny the things people will do for swag. I was talking to someone at the booth and had my back turned to the goods. I looked around and saw someone with a hand full of visors trying to sneak out. It looked like he made a sneak attack with his hand through the other booth over to ours.

We’ve made it a game to see how many inflated beach balls we can give out instead of uninflated ones. It’s funny to watch people walking around the expo hall with huge beach balls in their hands. I can just see them trying to fit them in their suite case on their way home.

Went to the US Party tonight. Lots of good food. Came in on the red carpet with our crew and took some funny pictures.

On to day 4.

I started out the day early going for a run with the famous Steve Chapman. Let me correct that. The “Silver Bullet” Steve Chapman. Man can that guy fly. I was thinking that he’d take it easy on me especially when he took me on a route that was rather shady in downtown LA. When we started out he bolted out of the gates and I was gasping for breath trying to keep up. After about three minutes I pretty much just said “Please stop.” He returned over his shoulder “I’ll meet up with you when I come back, don’t get mugged too bad” I doggedly hurried up to follow at least within shouting distance in case Steve had to save me from a early morning mugger.

After about fifteen miles we eventually ended back at our hotel drenched in sweat and with a commitment never agree to run with him again. I’m not sure how anyone finds running fun. I like to do it just to say I’m a runner but deep down I’d rather be sleeping at five a.m. in the morning.

After getting everything settled at our booth last night we were able to enjoy the key note sessions today. It’s always pretty neat to see all the cool toys and innovative technologies Microsoft has at it’s disposal.

Steve Ballmer showed the Xbox voice activated functions coming. He also announced live TV coming to the Xbox. He spent quite a few minutes on the Dynamics product lines and stated for the last ten years the accounting and business application area has grown 20% year after year.

Once again Navision was stated as being the first ERP product to move to the cloud. I guess 2012 will be the big release target.

Tammi Reller presented the Windows portion of the program. Nice to see her still around. It seems like most of the old GP guys are gone.

Linda rose presented a session on our cloud offering which was attended by over 300 people. We had quite a bit of traffic coming by the booth to thank her for her presentation.

Long day at the booth today. We had an eight hour stretch of people coming by to pick up beach balls and visors. Lots of traffic from all over the world. There really wasn’t that many pure GP people coming around to see us. For some reason last year there seemed to be more Dynamics folks.

We have a myGPcloud booth for anyone that wants to offer a Dynamics hosted offering. A big release is our Spanish version of myGPcloud. It’s a great offering for our LATAM friends.

We have the incredibly knowledgeable Francisco Hillyer at our booth for all you LATAM people.He’s pretty much translated our whole myGPcloud site into a Spanish version.

Ended the night with a very nice outdoors party with the Yammer folks. It was right across the convention center and the venue was really great.

I better get to bed. I think Steve is going to drag me around early in the morning on a rickshaw or something so he has added resistance to his early morning routine.

Day 3 coming up.

WPC is just starting today and I already feel like I’ve been here a week. Well, I actually have been in So.Cal. since Wednesday so it has been five days already. Since our main office is in San Diego I came down from the Great White North to rub shoulders with my fellow consultants and a few clients.

I hear there is over 15,000 people attending this years conference. Amazing really to have that many technology people together at one time. It’s a great time to learn, network and be inspired together.

We set up our booth today and got all of our swag ready. We are at booth #1150 if anyone wants some cool stuff. We have full size beach balls this year. My kids love hitting them around the house even if my wife doesn’t necessarily agree that’s the best use for them.

I’m looking forward to the keynotes especially. It’s always fun to listen to Steve Ballmer get excited and make snide comments about Apple. They also usually show off all the awesome things that are coming out in the future.

Tomorrow starts all the fun. I’ll keep everyone updated on the events and announcements here.

I was reading a blog by Kelsey Andrich over at I.B.I.S. I totally agree with her opinion on burning bridges.

Today I received a call from a long time client that moved over to Microsoft support for various reasons instead of using our support offering. I remember two or three years ago wishing the client the best of luck with Microsoft. Microsoft has good support and thought she’d be taken care of with them. Of course I told her they weren’t as good as me. Ha. But I really did hope things would go well for them.

After several years of using MBS support she has return to use our support offering. She apologized profusely for moving away from our support. Not because she had a bad experience with MBS but she thought I may be offended of the decision they made several years ago.

My immediate response was “I always want what’s best for the client. If that was the right business decision for you I’m glad you did it. No hard feeling. I’m glad we have the chance to support you again.”

I’m glad the client still felt she had a solid bridge to walk back to us on when the time was right.

Thanks for the reminder Kelsey.