{"id":521,"date":"2023-05-19T21:44:00","date_gmt":"2023-05-19T21:44:00","guid":{"rendered":"https:\/\/automatethemundane.com\/index.php\/2023\/05\/19\/hours-tracker-part-2-hours-tracker-table-build\/"},"modified":"2023-06-29T15:21:03","modified_gmt":"2023-06-29T15:21:03","slug":"hours-tracker-part-2-hours-tracker-table-build","status":"publish","type":"post","link":"https:\/\/automatethemundane.com\/index.php\/2023\/05\/19\/hours-tracker-part-2-hours-tracker-table-build\/","title":{"rendered":"Hours Tracker Part 2-Hours Tracker Table Build"},"content":{"rendered":"\n

5\/19\/2023<\/p>\n\n\n\n

\n\n\n\n

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

This part of the guide will go through two sections. <\/p>\n\n\n\n

Building Dataverse tables <\/p>\n\n\n\n

Extending built in Datavere tables. <\/p>\n\n\n\n

It is best to extend the user table and build a relationship between the new custom tables, instead of building a whole new user table. This minimizes the number of tables in the environment and allows us to build reusable columns that may be used in different solutions.<\/p>\n\n\n\n

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

Using the previous guide <\/a>and ERD we will make the two tables and extend the user table. <\/p>\n\n\n\n

Solution<\/h2>\n\n\n\n

The solution for this project will be called Hours Tracker. <\/p>\n\n\n\n

\"\"\/
Solution<\/figcaption><\/figure>\n\n\n\n

Custom Tables<\/h2>\n\n\n\n

Project Table<\/h3>\n\n\n\n

The Projects table is \u201ccurrently\u201d a single row table. It needs a primary column called Project Name. <\/p>\n\n\n\n

\n
\n
\"\"\/
Project Table<\/figcaption><\/figure>\n\n\n\n

Team Table<\/h2>\n<\/div>\n\n\n\n
\n
\"\"\/
Primary Column<\/figcaption><\/figure>\n<\/div>\n<\/div>\n\n\n\n

The Team table will be the same as the projects table. It is a single column table Team and Team Name.<\/p>\n\n\n\n

\n
\n
\"\"\/
Team Table<\/figcaption><\/figure>\n<\/div>\n\n\n\n
\n
\"\"\/
Primary Column<\/figcaption><\/figure>\n<\/div>\n<\/div>\n\n\n\n

Hours Table<\/h3>\n\n\n\n

The Hours table is a custom table that uses a primary column called Hours Name. This will become an auto number column that we will need to adjust after its creation. <\/p>\n\n\n\n

\n
\n
\"\"\/<\/figure>\n<\/div>\n\n\n\n
\n
\"\"\/<\/figure>\n<\/div>\n<\/div>\n\n\n\n

Edit the column to be a auto number column. <\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

Custom Columns<\/h3>\n\n\n\n

We are going to create three custom columns<\/p>\n\n\n\n

    \n
  1. Hours\n
      \n
    1. Name-Hours<\/li>\n\n\n\n
    2. Type-Decimal<\/li>\n\n\n\n
    3. Required-Yes<\/li>\n\n\n\n
    4. Min value-.05<\/li>\n\n\n\n
    5. Max Value-14\n
      \"\"\/<\/figure>\n<\/li>\n<\/ol>\n<\/li>\n\n\n\n
    6. Lead Verify\n
        \n
      1. Name-Lead Verify<\/li>\n\n\n\n
      2. Type-Yes\/No\n
        \"\"\/<\/figure>\n<\/li>\n<\/ol>\n<\/li>\n\n\n\n
      3. Date Worked\n
          \n
        1. Name-Date Worked<\/li>\n\n\n\n
        2. Type-Date Only<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n\n\n\n
          \"\"\/<\/figure>\n\n\n\n

          Lookup Columns<\/h3>\n\n\n\n

          There are two lookup columns in the Hours table<\/p>\n\n\n\n

            \n
          1. Projects\n\n
              \n
            1. Name-Project<\/li>\n\n\n\n
            2. Type-Lookup<\/li>\n\n\n\n
            3. Related Table-Project<\/li>\n<\/ol>\n\n\n\n
              \"\"\/<\/figure>\n<\/li>\n\n\n\n
            4. User\n
                \n
              1. Name-User<\/li>\n\n\n\n
              2. Type-Lookup<\/li>\n\n\n\n
              3. Related Table-User<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n\n\n\n
                \"\"\/<\/figure>\n\n\n\n

                \ud83d\udca1 Note: I decided to use the User table and extend it out vs creating joiner table.<\/p>\n\n\n\n

                In the next guide we will go through extending the User Table and building a N:N relationship to the Projects Table. <\/p>\n","protected":false},"excerpt":{"rendered":"

                5\/19\/2023 The Why This part of the guide will go through two sections. Building Dataverse tables Extending built in Datavere tables. It is best to extend the user table and build a relationship between the new custom tables, instead of building a whole new user table. This minimizes the number of tables in the environment […]<\/p>\n","protected":false},"author":1,"featured_media":258,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,35],"tags":[25],"class_list":["post-521","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-design","category-tables","tag-dataverse","entry","has-media"],"jetpack_featured_media_url":"https:\/\/automatethemundane.com\/wp-content\/uploads\/2023\/06\/cover-6-scaled.jpg","_links":{"self":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/521","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=521"}],"version-history":[{"count":1,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/521\/revisions"}],"predecessor-version":[{"id":527,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/521\/revisions\/527"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/media\/258"}],"wp:attachment":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/media?parent=521"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/categories?post=521"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/tags?post=521"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}