Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About Jo_In_Oz

  • Rank
  • Birthday December 23

Profile Information

  • Industry
  • Gender
  • Location
    Bramston Beach Queensland Australia
  • Interests
    Sustainability, Growing food, Eating food, Good movies

Contact Methods

  • Website URL
  • Skype

Recent Profile Visitors

1,625 profile views
  1. Perhaps the issue is, due to my inexperience, I have been lost on how to create the Sections, sub-sections and answer you describe, drawing questions from the master table . In my case there are 6 Sections, 91 sub-sections, approximately 1000 individual questions (not repeated anywhere). I think I am missing the point on how to the relate the records from sections to sub-sections. I think I have been thinking fields, rather than records, perhaps. I still get stuck thinking that each Indicator question needs to be hardwired to a field name in a table, rather than a record in the parent table. Once I see how the relationships, tables and layouts look, I will be closer to that AHAA! moment. Am I closer? Anyone?? If so, I think I get this now: I have a better understanding of the layouts advise now too: and Makes a bit more sense now as I begin to understand the flexibility of layouts and portals. Cheers, Jo
  2. I will want to summarise reports, so separate records for each answer is necessary. The seemingly attempt to create seperate tables for categories was more of an attempt to display the kinds of questions/data I am dealing with, and in the case of the indicators, not committing all indicators to one table until I am confident of how to work with and display the data. Some fields may need to be repeated as there are a number of entries required such as Materials used, which will usually have a number of different types and amounts (weights) of materials used. and Is it possible to have one field with a number of records that remain in the one overall record? Related to the one client? Yes, it will be complicated. Although without the structure of FMP to work within, it will be far more complicated to keep the data together and reporting for assessments and actions. Imagine trying to do all this with excel, as many do. It will be worth it in the long run, maybe even create an app! Yes, ambitious I know. Just confirming I am committed to an outcome as hard work now will improve productivity and accuracy later. Thanks again
  3. I have a table called GRI codes that has all the 91 records of the 91 sub-categories. I use the Global Reporting Initiative (GRI) codes to standardise reporting to a globally accepted practice. If I put all the Indicators in one table (yes, indeed hundreds of them), I will need to identify which ones are associated with respective GRI codes (the 91). Is there any danger in adding the GRI prefix, for example EN01, to field names related to the sub-categories eg EN01? I note in a previous comment you mentioned this was not advisable. My lack of experience with FMP presents a road block in that I do not know how to link the GRI codes table with its 91 records to the Indicators table. I have the relationships in place but I don't know how to display the required fields for entry. I have experimented with setting up the tables as suggested, although for now I have kept Indicators represented by the 2 tables EN01 GRI indicators and Indicator questions. I can bring all together into the Indicators table once I am sure that is the best option. Do I need to run scripts or adjust field behaviours? I have tried a few but to no avail, yet. There are issues to do with page layouts for data entry as mentioned, but the architecture is the first issue, then onto building. I have added a few images to provide some context to the kind of data requiring entry. The Responses table, (which I have called Client_Indicators), is a join table between Indicators and Reports. Reports will provide a sustainability assessment for the client, and will be repeated to provide comparisons to previous assessments. I considered an Actions table associated with Response, to provide an action plan for the client to improve sustainability, based on the data in Responses. The fields in Actions are the same as Responses, so this leads me to believe I can extend the Responses table to provide options for each indicator. It is my intention to grow the list of possible actions for each indicator as my experience as a sustainability consultant grows. I would need to relate the appropriate actions back to the client in a report called Action Plan. The Relationships image shows the current setup following the suggested ERD, although with 2 EN01 tables until I modify that aspect. GRI EC01 shows a layout that indicator. Is it possible make a layout that would display those particular indicators each time, related to a client? EN01 Indicator questions shows a range of questions associated with EN01 that are used to assess sustainability. EN01 indicators shows the GRI indicators for EN01. This one is tricky as it has multiple entries for easc field, roughly displayed in Raw materials used. Each entry must also be qualified with External or Internally purchased or sourced and if a renewable material or not. The number of entries required will vary with each client. It's a complex undertaking for a newbie, but I do tend to dive into deep ends and then swim like hell. Thanks for you help so far, Jo GRI EC01 indicators layout.tiff EN01 Indicator questions.tiff EN01 indicators.tiff Relationships.tiff
  4. Thank you! As I have continued to work on this I have come to a similar diagram. I have been experimenting with 6 tabs for the main categories/subjects on the client_indicator/response layout. Each tab has, for experimental purposes, 9 slides with portals related to the sub-categories with the respective indicator fields showing. What do you think?
  5. I was thinking of setting up 6 tabs to display 6 main categories, then 9 slides in each tab, with portals related to 9 tables requiring data entry. Will this work? So far I have not been able enter data in the portal/s. This could be my own lack of experience with portals though.
  6. I have included EN01 Indicator questions from the sub-categories related to the main category of EN, being for environmental indicators, to show the kind of questions asked. These questions could have Yes/No buttons. The relationship image shows the the graph when I made the FMP_Cl layout in the previous post. This is where I was trying out a child table to both clients and indicators with layout including a portal from indicators. It worked ok but I couldn't get it to automatically generate the records per client. What I am hoping to achieve is that I can use one layout that is child related to both client and sub-categories, with a portal related to sub-categories that has a dropdown/popup list with value lists attached, then select the appropriate sub-category, which will display the associated GRI indicators and the practical questions for data entry. Once I enter the data for that sub-category, I can then select the next sub-category and enter data into its GRI indicator and question fields/records and so on. All the time this data will be related to one client and the record of all this data has been automatically generated with its own id. I think that is what Snozzles is suggesting, which gives me some hope that I may be on the right track. EN01 Indicator questions.tiff relationships.tiff
  7. Thank you so much for your time and effort in replies. I will attach a few images that will hopefully clarify what I am working with. I am the user at the moment. I will go to businesses and assess their levels of sustainability based on the widely accepted GRI (Global Reporting Initiative) documents. To do this I have extracted the dry data requirements held within the GRI and I am also developing questions of a more practical nature to apply during visits to businesses (with my iPad), rather than relying purely on GRI driven data. My aim is to develop a system that can report using the GRI structure, while making practical assessments resulting in practical advice for clients. So the reporting is 2 fold, one based on the GRI to gear towards inevitable compliance and the second to provide clear information for clients as to where they are now in terms of sustainable practices, how to improve, and then later show reports on their improvements environmentally, economically and socially. The 6 main categories, with the subsequent 91 sub-categories come from the GRI. The relationships window image gives an overall example of the ideas I have been working with. The FMP_EC01 image is an example of one of the 91 sub-categories with its indicators requiring data entry. The sub_categories image shows an incomplete list of the 91 sub-categories to give an idea. The FMP_Cl image is from a stage where I had clients>clients_indicators<EC01 indicators related with a portal linked to EC01 indicators. I couldn't get clients_indicators to automatically provide a record and it also was related to one table, being EC01 indicators (in this case from the GRI). Snozzles, I think I get what you mean. I will study your post and get my head around it. In the meantime, I hope the above details help provide a better basis for your much appreciated advice. Thanks again, Jo FMP_CI.tiff FMP_EC01.tiff Relationships window.tiff sub_categories.tiff
  8. Hi comment, I didn't get into detail as it gets a bit confusing so I tried to give an example. Here is my situation in full: I am setting up a sustainability assessment database and am new to FMP, and I am not sure on the best way to set things up. I have 6 main assessment categories arising from economic, environment and social issues. These categories have a collective 91 sub-categories. The first main category, EC for economic, has 9 sub-categories. The second main category (EN for environment) has 34 and the last 4 are categories based on social indicators with sub-categories ranging from 9-12. The first sub-category in EC (the main category) is EC01 (the sub-category), and it has 10 indicators requiring data entry. Each sub-category has a number of indicators requiring data entry. Every record needs to be related to a client, who will want a report on their current level of sustainability and future reports on how they have improved, based on the action plans I provide, based on the assessment data. This means I also need to relate the fields in the assessment to another table with actions designed to improve the client’s sustainability. This section of the database will grow with my experience. Most of the main categories have 9-12 sub-categories (except EN, which has 34), and each sub-category has several items requiring data entry (eg EN01 has 10). This leads to 91 sub-categories, all with there own list of indicators that require data entry, leading to hundreds of fields requiring data entry. This may be difficult to work with if I cant find a way to split them up while maintaining relationships with the client. Should I make tables for each sub-category, so for example EC01 with its 10 fields would be in one table? I thought a join table called 'client_indicators' between clients and indicators would work, but then EC01 and all the others would need to relate to 'client_indicators'. I hope this makes some sort of sense.
  9. Thanks for all your help with the above. Perhaps another way of describing my situation is as follows: If a database had a services table with six different categories of services, and each category had variations, from 9 up to 34, totalling 91 categories, and each of those categories had a number of fields requiring data input, both qualitative for example pricing and qualitative for example quality of service, how would this be set up in a table so the user can choose particular services to enter data on? I keep coming back to the need to create tables for each of the 91 categories. And then if customers used the same services over time, how would the relationship be shown in that many to many relationship? ie many customers using many services. All this data will eventually produce reports to show trends, based on initial data inputs. Most importantly, where would the data be entered? Perhaps in the services table with the customer foreign key in place? Would a join table with both product and customer foreign keys produce the records produced in the products table? If so, how to access that information? Perhaps by putting in fields from the parents? I am hoping I am on the right track. Which table to use to input data is confusing me currently. Maybe a portal in the join table if one is required?
  10. I set up following your instructions. I am not sure where to place the fields that hold the questions. If in the 'questions' table, then I need to have the 'person' foreign key there I guess. My first sub-category (of 91) has 10 questions that require data input into the question fields. If I populate the questions layout with all the questions it will ge very long. Or do I start at the top, at person or category, and work my way down through droplists or similar. The questions need to be stored somewhere. Where do you suggest?
  11. The term zero to many is new to me. From what I have picked up it is the same as one to many with the option of one being zero, ie an empty field. This is perfect for me as not every person will answer every question. Makes no difference to the set up though, does it?
  12. Thanks for the links Kris M. There shouldn't be too much in the way of calculations, just relationships linking records together. Thanks dwdata, that does make sense. I am new and don't have a great grasp of how things link up yet, but your solution gives me a broader idea of what I can do. I will try it out! In the meantime, here is a summary of sorts, of where I have travelled so far in my first adventure with FMP. I have only constructed one table on the question side of things, so far, to experiment with. It represents one sub-catergory (called EN01) of 9 in the main category of ENs, ie EN01 to EN09. There are 5 more main categories eg, EN (with 34 sub-categories), LA, HR etc. Each sub-category has fields that required data entry. Each record of data that answers a question needs to be related to another field representing answers. Q: You could ask how many blue socks at this time? Ans: '100' then relates to a table with possible solutions to how to change the number of blue socks. So a main category could be clothing, another could be furniture etc with sub-categories being socks and chairs etc. Because there are so many different types of clothing, with colour and material etc possibilities, and each record relating to a person, I am not sure how to set up a solution with so many records and to do it in such a way to be able to report the current situation and then report on how things are at a later date for comparison. So far, as far as relationships go, I have tried a few different relationship setups. I see many 'persons' to many 'questions', because the questions are asked again later from the same person, so I tried a join table of 'persons questions'. So I had _pk_person_id related to _fk_person_id in the join table 'person question' and in the sub-category table I had _pk_EN01_id related to _fk_EN01_id in the join table 'person question'. I then built a portal related to EN01 in the 'person question' layout hoping that the data I entered into the 'EN01' layout, using a related '_fk_person_id' field to link that record to a person, would show up on the 'person question' layout. That didn't seem to sync. I now have 2 TOs for 'person question', one related to 'person' and the other to 'question'. It works in displaying data, much like the first setup, but it doesn't automatically show up the data from the EN01 records, I have to create a new record and select the person. The data does show up then. I go ahead now and get my head around your suggestion. I am sure I will learn a lot on the way. Thanks again for your help.
  13. Is there a limit to how many fields a table should have? My solution requires the management of 6 categories. Each category has up to 34 sub-categories, although most have 9-12, and each sub-category has several items requiring data entry. This leads to 91 sub-categories and hundreds of fields requiring data entry. The records per sub-category require relationships to corresponding sub-categories that respond to the data recorded. It is like a question/answer system, so the initial question field has data entered that requires answers entered and/or available (from previous experience) in the corresponding answer table. I have been thinking that I need to have the 91sub-categories in their own tables to limit the number of fields per layout. Is this a limitation? Is there anything wrong with having loads of fields in one table? Each of the 91 sub-categories will need to be related to the entity asking the question and receiving the answer. The questions and answers will eventually be presented in a report. In the future, when the answers have been acted on, the process is redone using the same sub-categories and the latest report is used to show progress, or otherwise. I could create tables for each main category and split the one with 34 sub-categories into 3, making 8 tables populated with the fields from the sub-categories which would result in approximately 50 to 100 fields in each table.
  14. A tad ambitious, but worth it in the long run to keep records together and to allow for a growing database of information as the knowledge base on actions to address unsustainable practices grows. Once the Indicator side of the solution is under control, then the actions to those measurements needs to be implemented. This side will grow as the knowledge base grows. I will keep plugging away at it, although any help will be greatly appreciated. Thanks.
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.