A Tutorial about the PPD Reform Tracking Tool on FileMaker

1. Public-PrivateDialogue A Tutorial about the PPD Reform Tracking Tool on FileMaker

2. The solution for the management problems of the facilitators • The PPD Reform Tracking tool was designed as an answer to the daily needs of the facilitators: • All the information regarding a reform is stocked in one place • In one click, you got the list of reforms that have been requested so far by the PPD • In one click, you know what the potential impact of the proposed recommendations is, per reform, per working group or in total • In one click, you can quantify the alignment of the reforms with the WB Indicators • In one click, you can quantify the number of reforms that were submitted to a specific jurisdiction

3. Part 1 : Data Entry

4. First layout: Issue Filing In this 6-page long layout you input all the information regarding an issue raised. Page 1 : the grey fields: put here the background of the issue the yellow fields: put here the reform title, type and the issue summary All the information is stocked in one place: the Issue Filing layout

5. Page 2: Economic Costs and Problems of the current situation: Here put a qualitative and quantitative description of the flaws of the current situation :the goal is to justify the need for reform All the information is stocked in one place: the Issue Filing layout

6. Page 3: Proposed Recommendations and their potential impact Here indicate your recommendations and try to quantify the annual cost and benefit to private sectors and governments that they can generate. All the information is stocked in one place: the Issue Filing layout

7. Page 4: Action Plans Describe specifically the action plan by giving law names and numbers that have changed or that are subject to change. Specify for each action plan, the jurisdictions that have been resorted to. All the information is stocked in one place: the Issue Filing layout

8. Page 5: Reform process and indicators Select one of the options concerning the progress of the issue in the Reform Process. Check the boxes for the WB Indicators Three types of World Bank indicators have been set up: Doing Business; Enterprise Survey Topics; Business Enabling Environment products. Select on each box to what extent the issue which is raised and tackled by this working group is in line with each type of indicators. All the information is stocked in one place: the Issue Filing layout

9. Page 6: The key documents Here in each field attach all the key – documents related to the issue by a “right-click and Insert file” To read the document again: right-click and Export Field Contents and save the document on your computer All the information is stocked in one place: the Issue Filing layout

10. Table View Form View List View Different possibilities to see all the records • FileMaker gives several possibilities for the screen views: • You can see one record (issue) at a time: this is the form view • You can see all the records (issues) as a list: this is the list view • You can see all the records in a table: this is the table view • To move from one view to the other, click on the button “View as” • To move from one record to the other in form view click on the book Click on the right page or left page of the book to move between records

11. The records can be printed or exported to pdf • To print or export, click on the “Preview “ button • Click either on the “Save as PDF” icon or on the print icon. • Decide if you want to print or export one record at a time or all the records being browsed. All that is seen on the screen can be printed as it is or exported to pdf

12. Training Exercise on Part 1 • Click on the icon “New Record” and input all the data of the case study • Then browse the information in list view and table view • Export the current record to pdf

13. PART 2 : Reports

14. The other layouts of the PPD Reform Tracking Tool • 7 other layouts (or reports) were designed to answer the needs of the PPD facilitators • Each one focuses on a specific set of information that was recorded in the Issue Filing layout. • The information of these 7 layouts does not need to be completed or modified. It comes from the Issue Filing layout. • Any update made on the Issue Filing layout updates the information contained in the other 7 layouts.

15. This layout focuses on 4 types of information of the Issue filing layout : Working Group, Reform Number, Reform Title, Indicators. In form view: you can see the alignment with indicators per reform In table view: you can see the information as if it was an excel spreadsheet. FileMaker gives the possibility to save this table in an excel spreadsheet (see next slide) Layout: Alignment with WB Indicators Form View Table View

16. Click on the button “preview” and then click on the “save as excel” button Give a name to the excel document that you create and decide if you want to export to excel all the records or one record at a time The records can be exported to Excel

17. This layout focuses on the submitter type and on the number of issues submitted by this submitter type Based on this information, it gives 2 statistics: the percentage of issues submitted by this submitter type and the total of issues submitted To display the data properly, click on the “sort by submitter type” button This layout has a bigger interest if seen in list view Layout: Number of Issues per Submitter Type List View

18. This layout lists all the reforms in numerical order If there are not listed in numerical order, press the “Sort by reform number” button Observe that you can not see this layout in form view. This possibility was cancelled because it is not interesting for this layout. FileMaker gives the possibility to forbid the access to one of the views for a specific layout (see next slide) Layout : List of Reforms in numerical order

19. Managing the layouts The change layout names or manage the access to views, go to File > Manage > Layouts Select a layout and then click on the button layout out set up Here you can change the name in the General tab and manage the view access in the Views tab

20. This layout lists all the reforms organized per Working Group If there are not listed in numerical order, press the “Sort by working group” button In form view, you will see all the reforms for 1 working group In list view, you will have all the reforms per working group Layout : List of reforms per Working Group

21. These layouts give the annual cost, benefit and total impact per reform and per working group They give also the total cost, benefit and impact at the PPD level Layouts: Potential Impact of Proposed Recommendations per Reform and per Working Group Press the button to display the data properly

22. This layout is in table view only It retrieves the information related to action items and jurisdictions that the reforms were submitted too. Save this layout as an excel table to draw statistics on the data. The goal is to be able to clearly determine how many and which reforms were submitted to each jurisdiction Layout: Action items and jurisdiction The Modify Button : it allows to reorganize the columns in the desired way : click on the double arrow to change the column order

23. These layouts were created according to the facilitators’ needs that we identified. You may want to create other layouts adapted to the needs of your own PPD and choose to display data in a different way To create a new layout : Go to View > Layout Mode click on the New Layout/Report icon Follow the instructions of the wizard Creation of New Layouts

24. Training Exercise on Part 2 • Answer the questions : • How many reforms can you count for the Working Group Bank ? • What is total impact of the proposed recommendations at the PPD level ? • What is the percentage of issues submitted by the submitter type “Private Company”? • Export to Excel the layout “Alignment with WB Indicators” • Create a standard form report with the fields Working Group, Reform Title, Current Status and pick up the theme that you prefer

25. PART 3: Customizing the existing layouts and Performing Find Request

26. To be able to customize the existing layouts, choose the layout that you want to modify and move to the layout mode through: Either the view mode (1) Or the bottom page menu (2) Or click on the ”Edit layout” button (3) 3 1 2 Customizing the existing layouts

27. In layout mode: resize, move and align fields • To resize fields: drag the handle at the lower-right corner of the field to the left until the field is smaller, yet large enough to display the longest data you expect in your file. • To move a field: click inside a field and drag it beside another field • To align fields: press shift and click on several fields; right-click Arrange > Align > and choose the way you want the fields to be aligned by the top, bottom, right or left edges. • To resize several fields to the same size ( to the largest/smallest width/height), press shift and click on several fields; right-click Arrange > Resize > and choose the way you want the fields to be resized

28. Right-click on a field and change either : The fill color The fill pattern The effect The Pen color The Pen widith The Pen Pattern In layout mode, change the color and effect of fields

29. For Number fields, (such as Cost Impact), right-click on the field and choose “Number format”: choose the notation (%, currency) and the decimal display For Date fields, right-click and choose date format : choose the appropriate options for your needs In layout mode: specify the number and date format

30. Double- click on an existing field of the layout out “Issue Filing” that present a list of values such as “Working Group, Current agenda, all the Indicators..” In the “Display Values from”, choose “ manage value list” (1) In the Manage Value List Box choose the value list that you want to edit, click on edit (3) and change the custom values or even the value list name 1 2 3 In layout mode: modify a value list field Value lists let the user choose the value he wants from a scrolling list, a pop-up menu, checkboxes, or radio buttons

31. Performing a find request means to find all the records that match one or a set of criteria (example: all the issues raised by the Working Group Bank and Tourism) To perform a find request on certain fields, you have to move to the find mode Either through the View Mode (1) Or by clicking the “Find Icon” (2) 2 1 Performing a find request

32. In Find Mode: find records matching criteria in a single field or in multiple fields • The criteria are entered in all the fields where you can see the “find symbol”. Put as many criteria as you want and click on the “perform find” icon • Results are given in the Browse Mode: only the records of the found-set can be browsed unless you click on “Show all” to get out from the found-set

33. In Find Mode: find records that match multiple criteria in the same field • Example: all the issues related to either the Industrial Relations Working Group or the Tourism WG. • Pick up your first criteria (Industrial relations) in the specific field (Working Group ) and click on the “ New Request” icon • Pick up your second criteria (Tourism) in the same field and click on the “ Perform Find” icon • You can add as many criteria as desired

34. A number of operators can be used to refine the search. They are accessible either from the status tool bar or from a right click on a specific field. Careful for the range operator, write the minimum bound before clicking on the range symbol (…) Example: you need all the issues submitted between march and april 2008: in the submitted date fields, type first 03/01/2008 then right click >operators, …range and then write 04/30/2008 In Find Mode: refining your search

35. Training exercise in Part 3 • Editing the fields: • Modify the size of the fields “reform” in the issue filing layout and resize to the width of the reform type field. Align both fields according to the left edge • Change the color and effect of the field working group • Change the number format of the cost field and give a currency • Modify the Value list working group to adapt it to your own PPD • Find the records matching these specific criteria: • Working Group Bank and Industrial Relations • Submitted in 2008 except in March • Whose potential annual impact is above $100m

36. PART 4: Create a new Database

37. You can create another database for other database : for example for Contact Management FileMaker gives templates for each type of use of a database Pick one template and adapt it to your need Creating a new database for other purposes