Detailed Instructions
Task 2: Creating Extended Attributes in the Database
1. Click on ToolsÆDatabase Painter on the PowerBuilder menu bar.
2. In the Objects view, expand the SybHealth profile, expand Tables, expand the doctor table, then expand the columns folder:
3. Over the dr_id column, right-click and select Properties from the context menu. 4. Properties appear in the upper-right of the Database painter.
5. Select the Headers tab.
Task 2: Creating Extended Attributes in the Database
1. Click on ToolsÆDatabase Painter on the PowerBuilder menu bar.
2. In the Objects view, expand the SybHealth profile, expand Tables, expand the doctor table, then expand the columns folder:
3. Over the dr_id column, right-click and select Properties from the context menu. 4. Properties appear in the upper-right of the Database painter.
6. Change the Label property to “ID:”. Change the Heading property to “ID”:
7. Save your changes.
8. Repeat this process for the dr_fname and dr_lname columns, giving them “First Name” and “Last Name” labels and headers respectively.
9. Close the Database painter.
10. Repeat Task 1, Steps 4 – 13, re-creating the doctors list DataWindow object. You will see the impact of adding Extended Attributes. The headers have your “pretty” names instead of using the column names. The Extended Attributes were automatically copied into your DataWindow object.
11. In the DataWindow painter’s Preview view, right-click and select Retrieve from the context menu. Drag the Last Name column (drag the header) in front of the First Name column. Notice how this updates the Design view as well:
6. Change the Label property to “ID:”. Change the Heading property to “ID”:
7. Save your changes.
8. Repeat this process for the dr_fname and dr_lname columns, giving them “First Name” and “Last Name” labels and headers respectively.
9. Close the Database painter.
10. Repeat Task 1, Steps 4 – 13, re-creating the doctors list DataWindow object. You will see the impact of adding Extended Attributes. The headers have your “pretty” names instead of using the column names. The Extended Attributes were automatically copied into your DataWindow object.
11. In the DataWindow painter’s Preview view, right-click and select Retrieve from the context menu. Drag the Last Name column (drag the header) in front of the First Name column. Notice how this updates the Design view as well:
12. Save the DataWindow object in the doctors.pbl as d_grid_doctors. 13. Close the DataWindow painter.
14. Extended Attributes provide more functionality than making headers pretty. They allow you to format the data for displaying and editing.
15. Right-click over the SybHealth_target and select New… from the context menu. 16. On the New dialog, select the DataWindow tab.
17. Select Tabular as the Presentation Style and click OK. 18. Select Quick Select and click Next.
19. Select the patient table and click the following columns: patient_id, first_name, middle_name, last_name, date_of_birth, state_code and phone_no columns. 20. Sort ascending based on the patiend_id:
21. Click OK.
22. On the Color and Border Settings dialog set the Text Border to Raised. Click Next. 23. Click Finish.
24. You should now be in the Design/Preview view of the DataWindow painter.
12. Save the DataWindow object in the doctors.pbl as d_grid_doctors. 13. Close the DataWindow painter.
14. Extended Attributes provide more functionality than making headers pretty. They allow you to format the data for displaying and editing.
15. Right-click over the SybHealth_target and select New… from the context menu. 16. On the New dialog, select the DataWindow tab.
17. Select Tabular as the Presentation Style and click OK. 18. Select Quick Select and click Next.
19. Select the patient table and click the following columns: patient_id, first_name, middle_name, last_name, date_of_birth, state_code and phone_no columns. 20. Sort ascending based on the patiend_id:
21. Click OK.
22. On the Color and Border Settings dialog set the Text Border to Raised. Click Next. 23. Click Finish.
25. In the Preview view, right-click and Retrieve.
26. Scroll right and make note of the way the date of birth and phone columns data displays:
27. Close the DataWindow painter, not saving the changes. 28. Re-open the Database Painter.
29. Locate and click on the Extended Attributes tab in the middle-upper portion of the painter:
30. Expand the Display Formats node. PowerBuilder automatically provides some default Display Formats, but you can create your own too.
31. Over Display Formats, right-click and select New…
32. In the upper-right of the painter, give your Display Format the Style Name of “SybHealth Date”, a Data Type of date, and a Format of “mmm-dd-yyyy”:
25. In the Preview view, right-click and Retrieve.
26. Scroll right and make note of the way the date of birth and phone columns data displays:
27. Close the DataWindow painter, not saving the changes. 28. Re-open the Database Painter.
29. Locate and click on the Extended Attributes tab in the middle-upper portion of the painter:
30. Expand the Display Formats node. PowerBuilder automatically provides some default Display Formats, but you can create your own too.
31. Over Display Formats, right-click and select New…
32. In the upper-right of the painter, give your Display Format the Style Name of “SybHealth Date”, a Data Type of date, and a Format of “mmm-dd-yyyy”:
33. Save (Ctrl-S) your work.
34. Creating the Extended Attribute is step one of the process. Step two is to associate the attribute to columns. This attribute can be created one time but used on many columns and on different tables.
35. In the Objects view of the Database painter, expand SybHealth, tables, patient and then columns. Right-click over the date_of_birth column and select Properties:
36. Go back to the Properties view of the painter. Click on the Display (for Display Format) tab.
37. Using the Display Format drop down, select SybHealth Date from the list:
33. Save (Ctrl-S) your work.
34. Creating the Extended Attribute is step one of the process. Step two is to associate the attribute to columns. This attribute can be created one time but used on many columns and on different tables.
35. In the Objects view of the Database painter, expand SybHealth, tables, patient and then columns. Right-click over the date_of_birth column and select Properties:
36. Go back to the Properties view of the painter. Click on the Display (for Display Format) tab.
38. Save your work.
39. Test by right-clicking over the patient table (in the Objects view) and select Edit DataÆGrid:
40. You will see the data in the Results tab of the Database painter:
41. Scroll right until you see your formatted date_of_birth column.
42. Extended Attributes created in the database are automatically copied into every DataWindow object that uses this column. You can remove or change the attributes after they have been copied into the DataWindow if necessary.
43. Over Display Formats on the Extended Attributes tab of the Database painter, right- click and select New…
38. Save your work.
39. Test by right-clicking over the patient table (in the Objects view) and select Edit DataÆGrid:
40. You will see the data in the Results tab of the Database painter:
41. Scroll right until you see your formatted date_of_birth column.
42. Extended Attributes created in the database are automatically copied into every DataWindow object that uses this column. You can remove or change the attributes after they have been copied into the DataWindow if necessary.
43. Over Display Formats on the Extended Attributes tab of the Database painter, right- click and select New…
44. Name this Display Format SybHealth Phone, Data type of String, Format of: @@@- @@@-@@@@:
45. Save your work.
46. In the Objects view, expand SybHealth, tables, patient and then columns. 47. Right-click over phone_no and select Properties.
48. Choose the SybHealth Phone Display Format on the Display tab and save your work. 49. Right-click over the patient table and select Edit DataÆTabular from the context
menu.
50. Scroll right to the phone number column to verify your work.
51. Note: the Database painter is nothing an end user will see. Don’t worry about resizing columns in its Results view. When you create the DataWindow objects you will align and size columns as needed.
52. Display Formats are for data display. Edit Styles are for data display and manipulation.
53. On the Extended Attributes tab, right-click over Edit Styles and select New…
54. Name the new Edit Style “SybHealth Gender”, with a Style Type of Radio Buttons.
44. Name this Display Format SybHealth Phone, Data type of String, Format of: @@@- @@@-@@@@:
45. Save your work.
46. In the Objects view, expand SybHealth, tables, patient and then columns. 47. Right-click over phone_no and select Properties.
48. Choose the SybHealth Phone Display Format on the Display tab and save your work. 49. Right-click over the patient table and select Edit DataÆTabular from the context
menu.
50. Scroll right to the phone number column to verify your work.
51. Note: the Database painter is nothing an end user will see. Don’t worry about resizing columns in its Results view. When you create the DataWindow objects you will align and size columns as needed.
52. Display Formats are for data display. Edit Styles are for data display and manipulation.
53. On the Extended Attributes tab, right-click over Edit Styles and select New…
55. Set the remainder of the properties as you see below:
56. Save your work (Ctrl-S).
57. In the Tree view, expand SybHealth, Tables, patient, Columns. 58. Right-click over the gender column and select Properties.
59. Click the Edit Style tab, select SybHealth Gender from the list box and save your work:
60. Edit your data using Grid style to test. Resize the gender column to see completely:
55. Set the remainder of the properties as you see below:
56. Save your work (Ctrl-S).
57. In the Tree view, expand SybHealth, Tables, patient, Columns. 58. Right-click over the gender column and select Properties.
59. Click the Edit Style tab, select SybHealth Gender from the list box and save your work:
61. Using the processes outlined above, assign the following Display Formats: Claims table: dr_charges and amount_paid columns:
62. Save your work. Display the data to make sure your claims table has the following dollar formats:
63. Close the Database painter.
64. Right-click over the SybHealth_target. Select New… 65. On the DataWindow tab, select Tabular and click OK. 66. Select Quick Select and click Next.
61. Using the processes outlined above, assign the following Display Formats: Claims table: dr_charges and amount_paid columns:
62. Save your work. Display the data to make sure your claims table has the following dollar formats:
63. Close the Database painter.
64. Right-click over the SybHealth_target. Select New… 65. On the DataWindow tab, select Tabular and click OK. 66. Select Quick Select and click Next.
67. Select the state table and Add All columns. Sort ascending by the state_code column:
68. Click OK and finish the wizard.
69. In the Design view of the DataWindow painter, remove the headers and drag the header band to the top of the view:
70. Click on the Tab Order icon on the PainterBar, setting both columns to a value of 0. Click again on the Tab Order icon.
71. Save this DataWindow as d_dddw_states in the sybhealth.pbl. The dddw indicates a Drop Down DataWindow.
67. Select the state table and Add All columns. Sort ascending by the state_code column:
68. Click OK and finish the wizard.
69. In the Design view of the DataWindow painter, remove the headers and drag the header band to the top of the view:
70. Click on the Tab Order icon on the PainterBar, setting both columns to a value of 0. Click again on the Tab Order icon.
71. Save this DataWindow as d_dddw_states in the sybhealth.pbl. The dddw indicates a Drop Down DataWindow.
72. In the Preview view, right-click and select Retrieve. Your data should look as follows:
73. Close the DataWindow painter and re-open the Database painter. 74. Create a new Extended Attribute, Edit Style named StateList. 75. Set the Style Type to DropDownDW.
72. In the Preview view, right-click and select Retrieve. Your data should look as follows:
73. Close the DataWindow painter and re-open the Database painter. 74. Create a new Extended Attribute, Edit Style named StateList. 75. Set the Style Type to DropDownDW.
77. Scroll further down the Properties view and set the remainder of the properties as shown below:
78. Save your work.
79. Associate the StateList Edit Style to the state_code column of the patient table. 80. When you view your data, it should look as follows:
77. Scroll further down the Properties view and set the remainder of the properties as shown below:
78. Save your work.
79. Associate the StateList Edit Style to the state_code column of the patient table. 80. When you view your data, it should look as follows:
81. Create another new Tabular DataWindow from the claim_reasons table as follows:
82. In the Design view of the DataWindow painter, remove the headers and drag the header band to the top of the view:
83. Click on the Tab Order icon on the PainterBar, setting both columns to a value of 0. Click again on the Tab Order icon.
84. Save this DataWindow as d_dddw_claim_reasons in the claims.pbl.
81. Create another new Tabular DataWindow from the claim_reasons table as follows:
82. In the Design view of the DataWindow painter, remove the headers and drag the header band to the top of the view:
83. Click on the Tab Order icon on the PainterBar, setting both columns to a value of 0. Click again on the Tab Order icon.
85. In the Preview view, right-click and select Retrieve. Your data should look as follows:
86. Close the DataWindow painter and re-open the Database painter. 87. Create a new Extended Attribute, Edit Style named ClaimReasons. 88. Set the Style Type to DropDownDW.
89. Enable the Always Show Arrow, Vertical Scroll Bar and AutoRetrieve properties:
85. In the Preview view, right-click and select Retrieve. Your data should look as follows:
86. Close the DataWindow painter and re-open the Database painter. 87. Create a new Extended Attribute, Edit Style named ClaimReasons. 88. Set the Style Type to DropDownDW.
90. Scroll further down the Properties view and set the remainder of the properties as shown below:
91. Save your work.
92. Associate the ClaimReasons Edit Style to the claim_code column in the claims table. 93. When you view your data, it should look as follows:
90. Scroll further down the Properties view and set the remainder of the properties as shown below:
91. Save your work.
92. Associate the ClaimReasons Edit Style to the claim_code column in the claims table. 93. When you view your data, it should look as follows:
94. Using the above steps, create another new tabular DataWindow for the claims_status table and its columns. Save the datawindow as d_dddw_claims_status in the claims.pbl:
95. Create a ClaimStatus Edit Style, then associate that Edit Style to the claims table, status_code column. Your data should display as follows:
94. Using the above steps, create another new tabular DataWindow for the claims_status table and its columns. Save the datawindow as d_dddw_claims_status in the claims.pbl:
95. Create a ClaimStatus Edit Style, then associate that Edit Style to the claims table, status_code column. Your data should display as follows:
96. Create one last tabular DataWindow to display from the insurance_coverage table. When finished, it will look as follows:
97. Save the DataWindow as d_dddw_insurance_coverages in the patients.pbl. 98. Close the DataWindow painter.
99. In the Database painter, create one last Edit Style named InsuranceCoverages. Associate InsuranceCoverages to the insurance_type column of the insurance_policy table. Your data will view as follows:
100. Create a new Validation Rule:
96. Create one last tabular DataWindow to display from the insurance_coverage table. When finished, it will look as follows:
97. Save the DataWindow as d_dddw_insurance_coverages in the patients.pbl. 98. Close the DataWindow painter.
99. In the Database painter, create one last Edit Style named InsuranceCoverages. Associate InsuranceCoverages to the insurance_type column of the insurance_policy table. Your data will view as follows:
101. Name the Validation Rule DoctorCharges. It will have a data type of number. Set the message to ‘Claims must be greater than or equal to $100.00’.
102. Click the Definition tab and use the @col placeholder to write the appropriate rule:
103. Assign DoctorCharges to the claims table, dr_charges column:
104. Save your changes.
105. Edit the claims table data in grid format.
106. Scroll down and you will see some doctor charges that are not greater or equal to $100.00. This means the validation rule does not fire on data retrieval. Validation rules only fire when a change is made to a column with an associated rule.
101. Name the Validation Rule DoctorCharges. It will have a data type of number. Set the message to ‘Claims must be greater than or equal to $100.00’.
102. Click the Definition tab and use the @col placeholder to write the appropriate rule:
103. Assign DoctorCharges to the claims table, dr_charges column:
104. Save your changes.
105. Edit the claims table data in grid format.
106. Scroll down and you will see some doctor charges that are not greater or equal to $100.00. This means the validation rule does not fire on data retrieval. Validation rules only fire when a change is made to a column with an associated rule.
107. On any row in the data display, change the dr_charges to 75.00 and press the tab key. You will see your custom error message.
108. Close the Database painter, answering No to the dialog asking you to “Save changes back to data source?”
107. On any row in the data display, change the dr_charges to 75.00 and press the tab key. You will see your custom error message.
108. Close the Database painter, answering No to the dialog asking you to “Save changes back to data source?”