Join table to link clientID to IndicatorID

[Jo_In_Oz]

I am setting up a sustainability assessment database and am new to FMP, and although I have worked through many of the main basic requirements, I seem to be stuck on how to connect one record in the 'clients' table and one in the 'indicators' table so that the join table, called 'client indicators' will show which clients have had which indicators assessed. I have the portal set up on 'client indicators' layout, but I can change the client and indicator records on the 'client indicators' layout. I need the client/indicator relationship to be static. I have 91 assessment tables to use eventually, having begun my development with EC01 Indicators to begin with. Is it my relationships setup or the dropdown lists?

relationships.tiff

GreenBusiness.fmp12

Edited January 25, 2016 by Jo_In_Oz

[webko]

How different to each other are these assessment tables? I shudder at the concept of creating 91 of them for a start....

Can a given client have more than one EC01 indicator? If not, then the join table shouldn't be required.

[Jo_In_Oz]

Thanks webko. Very different as the various indicators assess everything from economic to environmental to social indicators. Each one of the 91 indicators has a few separate indicators within the one indicator code. It is a huge undertaking, although the power of FMP is that all that data can be stored and linked to provide advice on actions to address the areas of unsustainable practices. I have found since I posted that I have cracked the locking of the clients to indicator, it seems my use of the value list, or something else, is not as it could be. Once I have this up and running, I can walk into a business with an ipad and go through the indicators and fill in the blanks, then go home and make a report with actions for each indicator that requires some level of action.

[webko]

Hi Jo,

I can't see an answer to the second question I posed...

Whereabouts in Aus are you? 

[Jo_In_Oz]

Do you suggest I just use the, in this instance, the EC01 indicator table and just ad a client portal? As time goes by, I will re-assess the indicator to see report on progress, meaning there will be a repeat of that indicator. That's why I went for the join.

Sorry, was typing my response. I am an hour south of Cairns. Paradise until you need to build a relational database from scratch without any experience.

[webko]

Depends on whether you want to look at this from the Client point of view, or the Indicators point of view.

I would have thought that the Client is the locus of the system, with 1...91 possible indicators attached to them - maybe I'm looking at this differently to you

If they can have the EC01 (or other) indictors re-done from scratch and you want to keep the historical data, then a join table is one approach. 

There are really 91 possible indicators, with little congruence between them? 

[Jo_In_Oz]

This is where I think my issues stem from as a newbie. I am not sure where to look from. Yes, I can see that the client focus is valid and probably the best place start, but then when I go to design the input of data side of things I end up at the indicators layout. Then to link up the clients and indicators I have the client indicators layout to see the data linking clients and indicators. So there will (hopefully) be many clients with many indicators, including the historical data, which form a baseline to measure, chart and report progress. FMP has the reporting power I hope to harness to communicate effectively with clients to get enthused about all things sustainable. If you open the fmp file and look up the GRI codes, you will see the kind of indicators. There are 6 basic codes, made up of 91 individual codes, each with individual indicators requiring measurements.

[Jo_In_Oz]

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.