Category Archives: Queries

GP Homepage Layout Mass Update – Thru’ SQL

Posted on by

I love SQL. Simply because, it eases lot of pain in doing some redundant work.

When you create a user, by default, GP creates the following segments on his/her homepage:

1. To Do (Reminders/Tasks)
2. My Reports
3. Microsoft Outlook
4. Quick Links
5. Connect (links to Online Resources such as CustomerSource, Forums, Blogs, etc.)
6. Metrics (quick dashboards for users)

All of the above work perfectly, but at some stages (especially when you are working on a GP Terminal Server) some of these components would halt GP for a while, not allowing users to do anything.

One classic example is Microsoft Outlook. We recently migrated our Email Services to Microsoft Office 365. For those users who use Thin Client to on their day-to-day job, Terminal Server is THE destination. Quite obviously, Terminal Server has got Outlook installed.

Before Office 365, Outlook profile used to connect to our on-premise Exchange Server. After migration, we had to change the profile from Exchange Server to Office 365 hosting server.

Whenever a user logs on to Terminal Server and opens GP, GP will halt for at least 3-5 mins to get this Outlook configured. I am getting calls from users (yes, even now) that their GP session does not work and they are not able to do anything. Following icon shows up on their taskbar when this happens:

It didn’t take me much time to realize that all these users had added Microsoft Outlook on their GP homepage.

“Do we really need Outlook on GP homepage?” is a question to be asked to oneself. Yes, Microsoft has integrated your ERP with one of the most used product by maximum business users. Agreed. But is that alone a reason for you to use it? Let’s not get into that argument in this post, anyways.

I had to take a stand and remove Microsoft Outlook from those users’ homepage. But then, I don’t know, right now, about how many users out of 100+ total users have added Outlook on their homepage.

Certainly not without the help of a SQL query.

The table where this information has got stored is SY08100 (Technical Name: syHomePageLayout). In this table, the column SectionID denotes the sections which are available for a user to add to his/her homepage. Following is the legend:

1 – To Do (Reminders/Tasks/Cues)
2 – Microsoft Outlook
3 – Metrics (quick dashboards for users)
4 – My Reports (links to users’ favorite reports and smartlists)
5 – Quick Links (links to users’ frequently used windows)
6 – Connect (links to Online Resources for GP such as Forums, Blogs, etc.)

Typically, for each user, this table will contain 6 rows, each row denoting each of above section. SY08100 also contains a column named Visible. This denotes whether or not to show a particular section on GP homepage.

For instance, if user SA has setup his GP homepage to show To Do, My Reports & Quick Links to show up, the following will be SY08100 records:

So, if I want to hide Microsoft Outlook from all users’ homepage, I just have to execute the following SQL query on DYNAMICS database:

UPDATE SY08100 SET Visible = 0 WHERE SectionID = 2

We must also understand one thing. Users can always add this again thru’ Customize this page… option. So this is NOT A PERMANENT SOLUTION.

Whenever there is a necessity, as in my case, you can certainly rely on this SQL method to do a homepage layout mass update.

VAIDY

Getting Month Numbers between two dates in T-SQL

Posted on by

I literally had to spend around an hour to crack this code, with some little help from my best geek friend, GOOGLE, of course.

Issue is this: I wanted to get Month in Numbers (Jan = 1, Feb = 2, etc.) that exist between two dates. For instance, if my date range is 1-Jan-2011 to 31-Mar-2011, then I should find and retrieve the months Jan, Feb and Mar as 1, 2 and 3 respectively.

After much much toiling and reading some stuff online, the following is what I got for myself:

—–

;WITH Numbers (Number) AS
(SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_ID) FROM sys.all_objects)
SELECT MONTH(DATEADD(MONTH, Number – 1, ‘2011-01-01’)) Month_Number
FROM Numbers
WHERE Number – 1 <= DATEDIFF(MONTH, ‘2011-01-01’, ‘2011-03-31’)

—–

IMPORTANT: Make sure that you have more than 12 records in sys.all_objects table, which by default SHOULD have.

I hope this code piece is useful for some who have got similar requirement.

VAIDY