| Topic | Presenter | Summary | Duration |
| ------------------------------------------- | --------- | ----------------------------------------------------------------------------------------------------- | -------- |
| Bulk Upload of Time Entries Using SQL in EP | Kevin K. | This is a short video walking you through how to bulk upload time entries using SQL in Easy Projects. | 9:46 |
00:00:07.340 --> 00:00:10.356 Alright, so we're going to do a quick00:00:10.356 --> 00:00:13.093 walk through on how to go through and00:00:13.093 --> 00:00:16.099 take the power BI time log Excel export.00:00:16.100 --> 00:00:17.787 And in Excel, go ahead and create00:00:17.787 --> 00:00:19.300 the SQL scripts and load those.00:00:19.300 --> 00:00:22.225 So you're doing a bulk upload of the time00:00:22.225 --> 00:00:25.235 logs and easy projects using a SQL script.00:00:25.240 --> 00:00:26.936 So what we have on the screen here00:00:26.936 --> 00:00:29.008 is an example file that one of the00:00:29.008 --> 00:00:30.737 PM's has exported and gone through00:00:30.737 --> 00:00:32.567 and specified the new project ID.00:00:32.570 --> 00:00:34.950 You'll00:00:34.950 --> 00:00:37.925 Uh, and the original task ID is00:00:37.925 --> 00:00:41.042 over here in column E and then the00:00:41.042 --> 00:00:43.310 time entry ID here in column B.00:00:43.310 --> 00:00:45.605 So what we're going to do is going to00:00:45.605 --> 00:00:48.125 select all these columns and go ahead and00:00:48.125 --> 00:00:50.287 use excel's feature to format and table.00:00:50.290 --> 00:00:51.930 And any format will work.00:00:51.930 --> 00:00:53.953 Uhm, just as long as you can00:00:53.953 --> 00:00:55.679 specify that it has headers.00:00:55.680 --> 00:00:57.465 And what we're going to do here00:00:57.465 --> 00:00:59.423 is just enter the SQL script line00:00:59.423 --> 00:01:01.121 that we're going to need here.00:01:04.010 --> 00:01:06.810 So from here we're going to use excel's00:01:06.810 --> 00:01:09.167 concat function to create the SQL script.00:01:09.170 --> 00:01:12.374 Uhm, necessary to insert the records00:01:12.374 --> 00:01:15.462 or update the records. So to do that,00:01:15.462 --> 00:01:17.579 we're going to just type an equal sign.00:01:17.580 --> 00:01:20.182 You can see here in the formula bar as well,00:01:20.182 --> 00:01:21.808 specifically if it's hard to see00:01:21.808 --> 00:01:23.923 in the column. But we're going00:01:23.923 --> 00:01:26.149 to start with the word concat.00:01:26.150 --> 00:01:28.380 And first, we're going to do at the00:01:28.380 --> 00:01:29.610 beginning part of our SQL script,00:01:29.610 --> 00:01:33.159 which is update TT underscore time entries.00:01:33.160 --> 00:01:35.548 This is the table in EP.00:01:35.550 --> 00:01:37.657 Uhm, that we're going to be updating.00:01:37.660 --> 00:01:39.644 So from here we're also going to then00:01:39.644 --> 00:01:41.750 get our next part of our SQL script.00:01:41.750 --> 00:01:46.000 It's going to be set task ID.00:01:46.000 --> 00:01:48.225 Space equals space and then00:01:48.225 --> 00:01:49.700 End Quote from here.00:01:49.700 --> 00:01:51.730 Going to enter a comma and we're00:01:51.730 --> 00:01:53.741 going to set the task ID to the00:01:53.741 --> 00:01:55.399 new project ID or new task ID.00:01:55.400 --> 00:01:58.767 This was specified by the project manager.00:01:58.770 --> 00:01:59.878 From here we're going00:01:59.878 --> 00:02:00.986 to answer another comma,00:02:00.990 --> 00:02:02.178 open our quotes,00:02:02.178 --> 00:02:04.554 its face and enter our where00:02:04.554 --> 00:02:05.423 conditioned where.00:02:05.423 --> 00:02:08.734 Time entry ID is equal to space00:02:08.734 --> 00:02:11.339 equals space in quote a comma00:02:11.339 --> 00:02:13.679 and in the time entry ID.00:02:15.900 --> 00:02:17.755 Close your parentheses and hit enter and00:02:17.755 --> 00:02:19.497 you should see the formula automatically00:02:19.497 --> 00:02:21.590 populate to the rest of the columns.00:02:21.590 --> 00:02:22.950 And you can see what we have here.00:02:22.950 --> 00:02:25.470 Update T underscore time it00:02:25.470 --> 00:02:28.526 reset task ID equal to 1875,00:02:28.526 --> 00:02:31.516 where time entry ID equals 303063.00:02:31.516 --> 00:02:33.658 So you also see some of these00:02:33.658 --> 00:02:35.577 down here that don't have it.00:02:35.580 --> 00:02:38.229 So what we're going to do is to only00:02:38.229 --> 00:02:40.070 show this formula where it makes sense.00:02:40.070 --> 00:02:42.350 It's come back to our formula00:02:42.350 --> 00:02:43.870 in the first column.00:02:43.870 --> 00:02:45.700 Go to the beginning and00:02:45.700 --> 00:02:48.050 put an if condition so if.00:02:48.050 --> 00:02:51.980 And our logical tests is going to be is.00:02:51.980 --> 00:02:52.294 Blank.00:02:52.294 --> 00:02:53.864 And then we're gonna specify00:02:53.864 --> 00:02:55.939 the new project ID column again.00:02:58.940 --> 00:03:01.530 And then a comma. So if is00:03:01.530 --> 00:03:03.388 blank, evaluates to true.00:03:05.500 --> 00:03:07.840 Then we want it to just be a blank cell.00:03:07.840 --> 00:03:10.304 If not, we wanted to use our existing00:03:10.304 --> 00:03:12.310 function that we've already written.00:03:12.310 --> 00:03:14.312 Come to the end of the formula00:03:14.312 --> 00:03:15.859 and put another code here.00:03:15.860 --> 00:03:18.088 And you should see.00:03:18.090 --> 00:03:19.860 A lot of these disappear,00:03:19.860 --> 00:03:22.724 which is exactly what we want to see.00:03:22.730 --> 00:03:24.194 From here you can take the00:03:24.194 --> 00:03:25.756 SQL script column, click it,00:03:25.756 --> 00:03:29.060 scroll to the bottom and uncheck the blanks.00:03:29.060 --> 00:03:33.358 And hit OK. This is going to00:03:33.358 --> 00:03:36.480 be what we copy out into SSMS.00:03:36.480 --> 00:03:37.690 From here you'll want to00:03:37.690 --> 00:03:38.658 connect to your order,00:03:38.660 --> 00:03:40.332 client or sonicwall clients,00:03:40.332 --> 00:03:42.840 depending on whichever one you're using.00:03:42.840 --> 00:03:44.046 In this case,00:03:44.046 --> 00:03:45.654 I'm using the forticlient00:03:45.660 --> 00:03:48.240 answering your password.00:03:48.240 --> 00:03:49.310 Wait for it to connect.00:03:52.240 --> 00:03:54.344 And once connected, go ahead and open SSMS.00:04:13.890 --> 00:04:15.976 Once SSMS is open, you'll want to00:04:15.976 --> 00:04:18.230 go ahead and connect to a server.00:04:18.230 --> 00:04:19.763 And in this case, we're going to00:04:19.763 --> 00:04:21.757 be connecting to SQL, a SQL 2014,00:04:21.757 --> 00:04:24.340 which is where the EP database resides.00:04:29.010 --> 00:04:31.012 So from here I'm going to click00:04:31.012 --> 00:04:33.606 into databases and look for the00:04:33.606 --> 00:04:35.520 underscore C MISHQ underscore00:04:35.520 --> 00:04:38.230 V 01 this is the EP database,00:04:38.230 --> 00:04:40.318 so I'm going to expand that and go00:04:40.318 --> 00:04:42.374 into tables and you'll see down here00:04:42.374 --> 00:04:44.248 you have your TT underscore time00:04:44.248 --> 00:04:46.202 entries table and that's the one00:04:46.202 --> 00:04:48.510 that we're going to be working with.00:04:48.510 --> 00:04:49.520 Couple different ways to go.00:04:49.520 --> 00:04:52.216 You can right click on the database new00:04:52.216 --> 00:04:55.285 query or you can right click on the table.00:04:55.290 --> 00:04:58.746 And just do a select top 100 rows.00:04:58.750 --> 00:05:01.326 This is simply just a shortcut to00:05:01.326 --> 00:05:03.158 specify the database you're using00:05:03.158 --> 00:05:05.774 so that it knows which one it's in.00:05:05.780 --> 00:05:07.196 You could also do a new query from00:05:07.196 --> 00:05:08.475 the base and then specify the00:05:08.475 --> 00:05:09.789 database in the strap down here.00:05:12.280 --> 00:05:13.246 From here we're going to hop00:05:13.246 --> 00:05:14.330 back over to our Excel file.00:05:16.430 --> 00:05:18.180 Click in column L in this case00:05:18.180 --> 00:05:19.773 and hit control in the UP arrow00:05:19.773 --> 00:05:21.440 to go all the way to the top.00:05:21.440 --> 00:05:23.946 Go down to the first record and00:05:23.946 --> 00:05:25.494 control shift down to highlight00:05:25.494 --> 00:05:26.916 all of the records in question.00:05:29.680 --> 00:05:30.770 Hit the wrong button there.00:05:30.770 --> 00:05:32.250 We're going to copy this.00:05:32.250 --> 00:05:34.314 You'll know it's copied when you see the00:05:34.314 --> 00:05:36.346 green dash is kind of going around here.00:05:36.350 --> 00:05:39.200 Flip back over to SSMS.00:05:39.200 --> 00:05:41.111 Do a control A in your query00:05:41.111 --> 00:05:42.160 and paste everything in.00:05:42.160 --> 00:05:44.840 It's going to do 1.00:05:44.840 --> 00:05:48.515 Ropert I'm log that's needing to change.00:05:48.520 --> 00:05:50.648 And in this case we'll take a look00:05:50.648 --> 00:05:52.788 at this project and we can see00:05:52.788 --> 00:05:54.024 that this is athlete.00:05:54.030 --> 00:05:57.110 So we can open up a web browser.00:05:57.110 --> 00:05:58.178 And go to EP.00:06:01.420 --> 00:06:03.168 Go into activities classic.00:06:05.990 --> 00:06:08.430 And we want to change this to athletes.00:06:17.500 --> 00:06:20.286 And then you can see where everything00:06:20.286 --> 00:06:22.974 currently is and in this case00:06:22.974 --> 00:06:25.800 I'm going to duplicate this tab.00:06:25.800 --> 00:06:29.184 And I'm going to come over to time logs.00:06:29.190 --> 00:06:31.990 And go over to the history view.00:06:31.990 --> 00:06:35.966 And I'm going to choose the athlete project.00:06:35.970 --> 00:06:38.594 I'm going to leave the time range I00:06:38.594 --> 00:06:40.640 had previously set of one one 2019.00:06:40.640 --> 00:06:43.520 And 12/22/2021 which is in the00:06:43.520 --> 00:06:45.152 future to make sure that I've gotten00:06:45.152 --> 00:06:46.538 all the time for this project.00:06:46.540 --> 00:06:47.269 And hit apply.00:06:51.280 --> 00:06:53.320 This is going to show me the total00:06:53.320 --> 00:06:55.311 on the project for billable is00:06:55.311 --> 00:06:57.645 869.41 and I'm going to use this00:06:57.645 --> 00:07:00.229 as an audit point here in a moment.00:07:00.230 --> 00:07:03.534 I'm going to come back over to SSMS.00:07:03.540 --> 00:07:05.868 And hit F5. This is going to execute00:07:05.868 --> 00:07:08.365 each of the individual lines 1 by 1.00:07:10.890 --> 00:07:12.400 And effectively update the values.00:07:15.860 --> 00:07:19.276 From here. I can flip back over.00:07:19.280 --> 00:07:22.192 And refresh this page I should be seeing00:07:22.192 --> 00:07:24.690 a number somewhere close to 869.41.00:07:33.820 --> 00:07:34.408 And I do.00:07:38.790 --> 00:07:41.984 If I come over here. And take a00:07:41.984 --> 00:07:43.772 look at some of these values.00:07:43.780 --> 00:07:45.215 You have a whole bunch of 0.00:07:48.920 --> 00:07:50.852 In this case, I'm actually gonna00:07:50.852 --> 00:07:52.869 filter by actual hours. Top to bottom.00:07:55.070 --> 00:07:56.960 And then just reapply my filter here.00:07:59.190 --> 00:08:00.380 And everything should now be00:08:00.380 --> 00:08:01.845 moved to the correct line item00:08:01.845 --> 00:08:03.214 that I was expecting you saw.00:08:03.214 --> 00:08:04.666 There was one time log entry00:08:04.666 --> 00:08:06.082 that was like 190 something00:08:06.082 --> 00:08:08.410 hours that has now been reduced.00:08:14.100 --> 00:08:17.350 So in this particular Excel file, the PM00:08:17.350 --> 00:08:20.470 notice that there was some. Items in red.00:08:23.980 --> 00:08:25.672 They were looking like they were00:08:25.672 --> 00:08:28.000 meant to go to different projects.00:08:28.000 --> 00:08:32.640 And he assigned this to 18789.00:08:32.640 --> 00:08:34.107 So what I want to do is figure out00:08:34.107 --> 00:08:35.448 did that go. It was supposed to.00:08:38.310 --> 00:08:40.606 So I'm going to look on this00:08:40.606 --> 00:08:44.550 webpage doing a control FD 18789.00:08:44.550 --> 00:08:46.566 And I can see that he's assigned00:08:46.566 --> 00:08:48.610 this to the credit line item.00:08:48.610 --> 00:08:51.274 Since this is a time log for another project,00:08:51.280 --> 00:08:52.292 it shouldn't be credited.00:08:52.292 --> 00:08:53.304 It should be moved,00:08:53.310 --> 00:08:54.540 so I'll communicate with the PM00:08:54.540 --> 00:08:56.241 to let them know that they need to00:08:56.241 --> 00:08:57.495 move this to the other project.00:09:01.010 --> 00:09:02.050 I continue to Scroll down,00:09:02.050 --> 00:09:03.751 I'm just gonna audit the other one00:09:03.751 --> 00:09:05.279 that they said was moved here.00:09:05.280 --> 00:09:06.975 So this is something that00:09:06.975 --> 00:09:09.240 looks like was a valid credit.00:09:09.240 --> 00:09:11.184 Come for the project.00:09:11.184 --> 00:09:13.614 So everything is good there.00:09:13.620 --> 00:09:15.268 From here I let the PM know and00:09:15.268 --> 00:09:16.896 let them know that this line item00:09:16.896 --> 00:09:18.745 needs to move to the bid project00:09:18.745 --> 00:09:20.330 instead of the athlete project.00:09:20.330 --> 00:09:22.206 So we didn't see any large disparities.00:09:22.210 --> 00:09:23.050 We can assume that everything00:09:23.050 --> 00:09:23.890 is good in the PM.00:09:23.890 --> 00:09:25.098 Didn't accidentally type in00:09:25.098 --> 00:09:26.910 a task from the wrong piece.00:09:26.910 --> 00:09:27.627 With that said,00:09:27.627 --> 00:09:29.300 that will conclude how we go ahead00:09:29.356 --> 00:09:31.124 and do a bulk upload of time entries.00:09:31.130 --> 00:09:33.390 Uhm, using SQL in EP.