{"id":609,"date":"2023-06-29T15:26:00","date_gmt":"2023-06-29T15:26:00","guid":{"rendered":"https:\/\/automatethemundane.com\/index.php\/2023\/06\/29\/hours-tracker-part-6-time-sheet\/"},"modified":"2023-06-29T15:26:00","modified_gmt":"2023-06-29T15:26:00","slug":"hours-tracker-part-6-time-sheet","status":"publish","type":"post","link":"https:\/\/automatethemundane.com\/index.php\/2023\/06\/29\/hours-tracker-part-6-time-sheet\/","title":{"rendered":"Hours Tracker Part 6- Time Sheet"},"content":{"rendered":"\n

7\/6\/2023<\/p>\n\n\n

\n\n\n

The Why<\/h1>\n\n\n

This part of the guide will go over the Canvas app portion of the build. The goal here is to build a very simple canvas app that will allow the users to add, update, and remove their hours. It will also serve as the location for leads to approve hours. <\/p>\n\n\n

The How<\/h1>\n\n\n

Initial Setup<\/h2>\n\n\n
    \n
  1. Create a blank Canvas app<\/li>\n\n\n\n
  2. Connect in the User, Hours, Project, and Team Tables. \n
    \"\"\/<\/figure>\n<\/li>\n\n\n\n
  3. On the App OnStart add the following variables. The varUser and varTeam are used to minimize the amount of looks to be done later. \n
    \/\/Get User and Team Data\nSet(\n    varUser,\n    User()\n);\nSet(\n    varteam,\n    LookUp(\n        Users,\n        'Primary Email' = varUser.Email\n    ).Team.'Team Name'\n);<\/code><\/pre>\n<\/li>\n\n\n\n
  4. Add a Date Picker Control and set it as required. Name it calinputTS.\n
    \"\"\/<\/figure>\n\n\n
    💡 Note: I am using a newer modern control in this application. I really love the look and feel of the new date picker. <\/p>\n<\/li>\n\n\n\n
  5. Set the Value to Today()<\/li>\n<\/ol>\n\n\n
    \"\"\/<\/figure>\n\n\n
    Today()<\/code><\/pre>\n\n\n
    Logic Setup<\/h2>\n\n\n
      \n
    1. We now need to configure a few variables and collections to get the data and time box it. <\/li>\n\n\n\n
    2. The first two variables we will create set the start and stop dates based on the calendrer selection. \n
      Set(\n    selectedStartDate,\n    DateAdd(\n        calinputTS.Value,\n        1 - Weekday(\n            calinputTS.Value,\n            StartOfWeek.Monday\n        )\n    )\n);\nSet(\n    selectedEndDate,\n    DateAdd(\n        calinputTS.Value,\n        7 - Weekday(\n            calinputTS.Value,\n            StartOfWeek.Monday\n        )\n    )\n);<\/code><\/pre>\n\n\n
      💡 Note: for all of the configurations here my week day starts on Monday. <\/p>\n<\/li>\n\n\n\n
    3. The Next two are the collections that gather the users authorized projects and any hours they may have already entered. \n
      ClearCollect(\n    colAuthorizedProjects,\n    LookUp(\n        Users,\n        'Primary Email' = varUser.Email\n    ).'Projects (ew_SystemUser_ew_Project_ew_Project)'\n);\nClearCollect(\n    colcurrentHours,\n    Filter(\n        Hours,\n        'Date Worked' >= selectedStartDate && 'Date Worked' <= selectedEndDate && User.'Primary Email' = varUser.Email\n    )\n);<\/code><\/pre>\n\n\n
      💡 Note: the \u201c).'Projects (ew_SystemUser_ew_Project_ew_Project)'\u201d is the N:N relationship that we setup in <\/p>\n<\/li>\n\n\n\n
    4. The Next collection is collection colworkdays. It is the time box so that when we add the days to the gallery only the days that fall within it show up. \n
      Clear(colworkdays);\nForAll(\n    Sequence(7),\n    Collect(\n        colworkdays,\n        {\n            DayName: Text(\n                DateAdd(\n                    calinputTS.Value,\n                    Value - Weekday(\n                        calinputTS.Value,\n                        StartOfWeek.Monday\n                    )\n                )\n            ),\n            Date: DateAdd(\n                calinputTS.Value,\n                Value - Weekday(\n                    calinputTS.Value,\n                    StartOfWeek.Monday\n                )\n            )\n        }\n    )\n)<\/code><\/pre>\n<\/li>\n\n\n\n
    5. All together these are loaded into the OnVisible property of the timesheet screen\n
      \"\"\/<\/figure>\n\n\n
      Set(\n    selectedStartDate,\n    DateAdd(\n        calinputTS.Value,\n        1 - Weekday(\n            calinputTS.Value,\n            StartOfWeek.Monday\n        )\n    )\n);\nSet(\n    selectedEndDate,\n    DateAdd(\n        calinputTS.Value,\n        7 - Weekday(\n            calinputTS.Value,\n            StartOfWeek.Monday\n        )\n    )\n);\nClearCollect(\n    colAuthorizedProjects,\n    LookUp(\n        Users,\n        'Primary Email' = varUser.Email\n    ).'Projects (ew_SystemUser_ew_Project_ew_Project)'\n);\nClearCollect(\n    colcurrentHours,\n    Filter(\n        Hours,\n        'Date Worked' >= selectedStartDate && 'Date Worked' <= selectedEndDate && User.'Primary Email' = varUser.Email\n    )\n);\nClear(colworkdays);\nForAll(\n    Sequence(7),\n    Collect(\n        colworkdays,\n        {\n            DayName: Text(\n                DateAdd(\n                    calinputTS.Value,\n                    Value - Weekday(\n                        calinputTS.Value,\n                        StartOfWeek.Monday\n                    )\n                )\n            ),\n            Date: DateAdd(\n                calinputTS.Value,\n                Value - Weekday(\n                    calinputTS.Value,\n                    StartOfWeek.Monday\n                )\n            )\n        }\n    )\n)<\/code><\/pre>\n<\/li>\n<\/ol>\n\n\n
      We also need to add logic to the date picker so that when a date is selected, the colection is updated. Copy the above logic and place it in the OnChange of the date picker. <\/p>\n\n\n
      \"\"\/<\/figure>\n\n\n
      Gallery Configuration<\/h2>\n\n\n
        \n
      1. There are two galleries to be added to the time sheet screen. The first is called galProjectsTS, it is the gallery for all the projects and will use the collection colAuthorizedProjects. The second will be a sub gallery in the galProjectsTS and be called galHoursTS, it is where all the hours are to be entered and is fed by the colworkdays collection. <\/li>\n<\/ol>\n\n\n
        galProjectsTS<\/h2>\n\n\n
          \n
        1. Add a blank vertical gallery and call it galProjectsTS set the items to colAuthorizedProjects<\/li>\n\n\n\n
        2. Add a label control and call it lblProjectNameHS. Set it to \n
          ThisItem.'Project Name'<\/code><\/pre>\n<\/li>\n<\/ol>\n\n\n
          \"\"\/<\/figure>\n\n\n
          galHoursTS<\/h2>\n\n\n
            \n
          1. Insert a blank hortintoal gallery inside galProjectsTS. Name it galHoursTS<\/li>\n\n\n\n
          2. Set the Items to colworkdays\n
            \"\"\/<\/figure>\n<\/li>\n\n\n\n
          3. Add a text label to the gallery and name it lblDayHoursTS. Adjust the label and the gallery to make it look like the following. \n
            \"\"\/<\/figure>\n<\/li>\n\n\n\n
          4. Update the Text for the label to the following\n
            Text(\n    ThisItem.Date,\n    "[$-en-US]ddd ,mmm dd"\n)<\/code><\/pre>\n<\/li>\n\n\n\n
          5. Add a text input and name it inputHoursTS. Set the input to be a number value and the hint text to 0<\/li>\n\n\n\n
          6. The next step is to add the default value of the input. This will search the collection to see if there are any hours for that given day. \n
              \n
            1. Set the Default value of the input to the following\n
              \"\"\/<\/figure>\n\n\n
              LookUp(\n    colcurrentHours,\n    Project.'Project Name' = lblProjectNameHS.Text && 'Date Worked' = ThisItem.Date\n).'Hours (eca_hours)'<\/code><\/pre>\n\n\n
              💡 Note: Yours hours name will be different then mine. <\/p>\n<\/li>\n<\/ol>\n<\/li>\n\n\n\n
            2. Next we will set what happens when an entry is either added or removed from the input. I have chosen to do this in the on change of the input. This was the simplest method when working in the sub galleries. \n
                \n
              1. Set the Delay Output to true. This will ensure that the patch dose not happen until the user clicks out of the text box. \n
                \"\"\/<\/figure>\n<\/li>\n\n\n
              2. Set the On Change to the following \n
                \/\/ First, look for an existing record and set it in "existingRecord"\nWith(\n    {\n        existingRecord: LookUp(\n            colcurrentHours,\n            'Date Worked' = ThisItem.Date && Project.'Project Name' = galProjectsTS.Selected.'Project Name' && User.'Primary Email' = LookUp(\n                Users,\n                'Primary Email' = varUser.Email\n            ).'Primary Email'\n        )\n    },\n    If(\n        !IsBlank(inputHoursTS.Text) || Value(inputHoursTS.Text) = 0,\n\/\/ Patch the record:\n        Patch(\n            Hours,\n    \/\/ If an existing record was found, update it; if not, create a new one:\n            If(\n                IsBlank(existingRecord),\n                Defaults(Hours),\n                existingRecord\n            ),\n            {\n                'Hours (ew_hours)': Value(inputHoursTS.Text),\n                Project: LookUp(\n                    Projects,\n                    'Project Name' = galProjectsTS.Selected.'Project Name'\n                ),\n                'Date Worked': ThisItem.Date,\n                User: LookUp(\n                    Users,\n                    'Primary Email' = varUser.Email\n                )\n            }\n        ),\n    \/\/Remove the record if removed from input\n        If(\n            !IsBlank(existingRecord),\n            Remove(\n                Hours,\n                existingRecord\n            )\n        )\n    )\n);\nClearCollect(\n    colcurrentHours,\n    Filter(\n        Hours,\n        'Date Worked' >= selectedStartDate && 'Date Worked' <= selectedEndDate && User.'Primary Email' = varUser.Email\n    )\n);<\/code><\/pre>\n<\/li>\n\n\n
              3. Set the Display mode as follows. This will disable the input if the lead has approved the hours. \n
                If(\n    LookUp(\n        colcurrentHours,\n        Project.'Project Name' = lblProjectNameHS.Text && 'Date Worked' = ThisItem.Date\n    ).'Lead Verify' = true,\n    DisplayMode.Disabled,\n    DisplayMode.Edit\n)<\/code><\/pre>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n\n\n
                The core of the canvas app is now done. In the next guide we will add in supervisors approval. <\/p>\n\n","protected":false},"excerpt":{"rendered":"
                7\/6\/2023 The Why This part of the guide will go over the Canvas app portion of the build. The goal here is to build a very simple canvas app that will allow the users to add, update, and remove their hours. It will also serve as the location for leads to approve hours. The How […]<\/p>\n","protected":false},"author":1,"featured_media":610,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[18,25],"class_list":["post-609","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","tag-canvas-app","tag-dataverse","entry","has-media"],"jetpack_featured_media_url":"https:\/\/automatethemundane.com\/wp-content\/uploads\/2023\/08\/photo-1553272787-2bbfd028da99-scaled.jpg","_links":{"self":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/609","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/comments?post=609"}],"version-history":[{"count":0,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/609\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/media\/610"}],"wp:attachment":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/media?parent=609"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/categories?post=609"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/tags?post=609"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}