Jump to content

Join table to link clientID to IndicatorID


Jo_In_Oz
 Share

This topic is 2346 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Jo_In_Oz
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2346 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

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