Relational Value Lists (do they exist)?

[sokorp]

Hello,

I can't wrap my brain around this problem but have an idea of what needs to happen generally. I think I need to create some simple calculations and Value Lists. I have started but have to admit I am basically stuck.

I am working for an INGO in Nepal and we are setting up a database. The database has fields for the governmental divisions of the country. Nepal divides it's country into 5 "Development Regions", 14 "Zones", 75 "Districts", 3,913 "Villages" and 335,217 "Wards". What I want to do is create and make value lists for each of the fields mentioned above in each record (that would be 5 fields). However I want the value lists for each field to only contain data that is relevant to the data in the previous larger, governmental area. Confusing?

An example based on the USA:

If I choose Illinois as the State for the State field I DON'T want Counties showing up in the County field that are in States other than Illinois, and then when I choose Cook County in the County field I only want Cities to show that are in Cook County like Chicago... and so on and so on.

I was thinking I could use numbers in association with the different areas

Sincerely,

John Prokos

[MoonShadow]

Do relational value lists exist? If you create relationships, John. And you are on target if that was your plan. For Conditional Value Lists to work, you need relationships. It's best to use records to hold your Districts, Villages and Wards for sure.

You have: 5 "Development Regions", 14 "Zones", 75 "Districts", 3,913 "Villages" and 335,217 "Wards".

You might consider this ...

I would create a Zone db with Region field (and Custom 'Region' Value list). It would also contain ZoneName and ZoneID. So you would have 14 records (zones) and would need to identify their Region on each record. You could then base your Zone Value List on field values. Districts, Villages and Wards should all be related files. With such a structure, Conditional Value Lists (which filter and only display related values), would be a snap.

I was thinking I could use numbers in association with the different areas

Yes. They are unique IDs and will maintain the relationships for you. Every file should have their own unique set. In addition, Wards would contain a VillagesID (text field). Villages would contain a DistrictsID. And Districts would contain a ZoneID.

In this way, if a new Ward is added, your Value List will update automatically ... on up the chain. It will be very easy to maintain and produce easy validation, reporting and upkeep on your Value Lists. I (or others) can provide an explanation on it if you decide to go with such a structure.

I discourage establishing everything in one file - and I even discourage having 5 fields to hold the 5 Regions - it's best if you had one field called Region and multiple records for Zones (each record specifying its Region). You may wish to eliminate the Zones db and start on Districts for your related file. If so, you will have a bit more work because you will need to enter both Region and Zone on each record in Districts.

Let us know your thinking on the structure and we then explain the Conditional part based upon your decision.

[sokorp]

I will study your response and attempt to implement it. It will take me a while to do this I am sure. Expect some time to pass before I can respond. Thank you very much for your help!

The "5" fields are just a small part of one layout that includes other fields mostly pertaining to an individuals bio-data (name, age, etc).

Regards,

John Prokos

[sokorp]

I deleted that last post. I used Stuffit to archive the files on my desktop and then posted them here. Somehow the main file became corrupted during that process and I lost all of my formatting and half of my fields. I have to start from scratch...

John Prokos

Kathmandu, Nepal

[MoonShadow]

Sorry to hear you're having a bit of trouble, John. If you could provide me with the pertinent fields in your main file (and your main file name), I could put together an example for you. Let me know if I can be of further assistance at this point ...

[sokorp]

Hello.

I have rebuilt my database... and I am posting it here. If you would please take a look and see. I have the "Region" and the "Zone" working perfectly. However I am not sure how to make the other three division (Districts, VDCs, and Wards) work as related files. I don't understand how they can be selectively associated if they are not on the same record and in the same file as "Region" and "Zone". I am also not sure why there needs to be a unique number associated with each... so far I am not using that field for anything. Instinctively, I thought that was the right thing to do but I am not sure how to implement it. It's been so long since I have worked with this software... FMP4. I hope you can see what my errors are in the file I have uploaded.

Thank you very much.

Database.zip

[MoonShadow]

Hello John,

I had a brief look at your files and will provide specific assistance with your file when I get home tonight if you need it. I have attached a simple example for you using a Zip Code file (only two states to keep the size small).

If your Wards information will be imported as a flat file, ie, you will receive a list of Wards including fields for the Ward's District, Zone and Region, you may simply wish to maintain all of this in ONE file as my example (zipcode). I originally suggested related but, after viewing your solution, I think we should keep this easy for you. I also don't believe that a ? is a very good idea in a field name (first field in MPDB).

Does your Wards data already contain fields for District, Zone and Region? Flat file or related structures can both be used. What matters is that the relationships established filter accordingly. And, since your understanding of uniqueIDs (and relational theory) is a bit slim, flat file would be easier. BTW, uniqueIDs should always be standard fields and never calculations. But this is a major subject in itself.

Oh. One more thing ... what is the maximum number of Wards that can exist within one District? If it is more than 15 or so, your Wards popup may need to be filtered further. This could simply be a radio button next to it with A-M and N-Z or some other device to split the display of Wards for you.

John, I put this post and demo together in a big hurry ... running late. I make no claims to its accuracy or logic - I just didn't want to leave you hanging all day without something to work with. This example file will show you clearly how to filter your Value Lists. Once you have that understanding, your solution should come together pretty quickly - no matter how you decide to handle it.

FilteredVL.zip

[Fenton]

I think you've got the idea. A quick look at your MPDB file: Zones shows that the relationship and value list are set up correctly, ie., on Region. But on the layout the Zones field has the District value list attached, not the Zones value list. Simple error.

[dbruggmann]

I agree, that it is theoretically quite simple to produce those related value lists. I attach a fixed version of your files.

But I doubt, that it will be practically. For example: For the Wards to work, you need to have them all in a file with 335,217 records

Relational Value Lists (db).zip

[MoonShadow]

Thanks Fenton and Detlev for adding information to this ...

Unless you happen to have the raw data in a form, where you can import at least Wards&Villages and Villages&Districts together, this is impracticable.

After reviewing his file, I believe he may have a full list available - as we can receive our USPS Address information - flat file containing Street, City, State and Zip. If so, it would be unnecessary to split them into different files. And that's why I 'changed my tune' about (possibly) separate relationships. If in flat file, it would be much easier to establish.

I also considered Regions, Zones, Districts and Villages as simply lookups - because if he can determine the Ward, he wouldn't need to even fill in the rest. I see this situation no differently than our address structure - ask someone for their zip code and the rest pre-fills. A Village popup with a radio button to further filter Wards would make it easier than using popup for Region, Zone, District, Village and Ward also.

The question is ... how does sokorp receive this data? Does he have a flatfile already containing all fields?

[Fenton]

1. dbruggmann is right, that for the Ward choice, you would have on average 86 choices. This is a fair amount for a value list, but I think OK. I began to build a more complex "choice" system, which used the first letters of the name (Clairvoyance) combined with the known filter (District in this case). The District & Village were concatenated (combined) for the right side, the letter entered in a global, the matches shown in a portal, in a narrow window of the Village file -- not the MPDB file.

This, IMHO, is the fastest and most efficient method to choose for large networked files. But, then I realized we're only talking 45-85 average values in the drop-down lists. The above method requires a layout, calculation field, global field, a few scripts, for navigation and setting data; it may be overkill, especially for a beginner.

2. I'm assuming the data is received as a flat file, otherwise it would already be in a relational database system. It could be utitlized as a flat file; because it's basically a "value list file." Self-relationship between the names could establish the connections for the related value lists. This would actually use the least amount of disk space, as there would be no duplicate data, as there would be with separate files.

3. The problem with using just names is that if you change a name (except a Ward), it must be changed in 2 files, both its own and its many intances in "child" file, or the relational connection will be broken.

4. The more I think about it, the more I think the flat file structure, using just the names, with self-relationships to filter, may be the way to go.

This is the logic: There is only 1 real "entity," with is the "location," the Ward in this case. All the others are really only "attributes" of the Ward. You are not interested in them as entities themselves; you are storing no other information about them that would require a separate file.

Each missing person is from somewhere, a Ward. If you do not know the Ward, then it is "unknown," which is a special instance of a Ward (you could have 1 "unknown" entry for each combo, if you even have unknowns).

A single flat file has the advantage of being more understandable to a beginner. Each entry is what it is, a combination of all the attributes to make one location. You would need to use Find & Replace if you change anything except a Ward, but it's all there in front of you. Dangerous perhaps, but simple.

5. However, we should talk also about why the "numbers," as first mentioned, are used in relational systems. This would be if you did the system as separate files. These would auto-entered serial IDs (not, as Sokorp began to implement, simple calculations using part of the name; these would likely have duplicates within the file, and are entirely dependent on the name).

If these real serial IDs were used, instead of the names, then it wouldn't matter if you changed a name.

6. Perhaps you don't care so much what happens when a name is changed. Maybe it very rarely happens. Maybe they completely move things around every once and a while, in which case you'd want to replace the whole value list file, but just leave the data that was entered as is.

7. Well, I've certainly muddied the waters. I guess I'll go try to solve my own problem now, filtering by account name in FileMaker 7.

[sokorp]

Thanks everyone for all of your suggestions. The truth is I am just beginning to refresh my memory of how FMP works. I have mangaged to acquire an Excel table from a fellow here in Kathmandu who is a real programmer. He says I should base this whole thing on number not on names, much more professional for various good reasons.

Regarding Wards: Wards in Nepal are just a number, usually 1-11 in the country but the number can reach 50 in the metropolitan areas. I just have to get another table or have someone type one up.

So an address would be broken down like this: "Central" Region - "Bagmati" Zone - "Kathmandu" District - "Dhapasi" VDC - "32" Ward.

Regarding not knowing the complete address. Since the address is not number based as in the USA everyone knows their complete address. There are no streets or street numbers or zip codes.

What I have done is make four look up tables (files) for each of Region/Zone, District, VDC, Ward (Region and Zone are in the same file because they are so few in records). I have three fields in each of the four files (except for Regions/Zones which has four - two id fields and two name fields). The fields in the other files consist of two ID fields and a name field.

"District" file fields: ZoneID, DistrictName, DistrictID, and the pattern continues for the other three files. "VDC" file fields: DistrictID, VDCName, VDCID; "Ward" file fields: VDCID, WardName, WardID

I have created five hidden ID fields in the main database that will hold the ID numbers of the respective related files using lookup based on a relationship with the name. This is the problem... i need this whole thing to run on numbers only, i just can't figure out how to tell FileMaker to do it! That's simply a sign of my ignorance of programming. Or tools to do it simply.

Thanks for suggestions.

John

[Fenton]

Well, we generally use IDs (text or number) not names. FileMaker is no different from other relational databases about this. In your case it seemed as if the names were fixed, so we let you off easy :-) Your friend is right however that IDs are usually better than names; in case they change a name.

They also take up less space - if you only reference the name. This is a decision you'll have to make, whether to "lookup" the names into the final data file, or only show them as related fields. Probably you will want to look them up, as it makes searches and reports easier for people. It will increase the size of the final file though.

OK, if you use IDs, then you must "serialize" your records. You want an ID field, auto-entered serial, either text or number. If you use text you may want to use a leading letter, and/or leading zeros.

This way any drop-down value lists will line up. The leading letters are optional. (Any leading has to be added if you use GetNextSerialNumber when updating; easy enough if you remember.)

I redid your files with IDs. It's quite different. I added a fancy routine to "filter" the choice for a village also, using a filtered portal based on a concatenated field (I said it was fancy :-). I didn't do it for a Ward choice; which it probably needs also.

I see also that I did all the layout work in MPDB on the Notes layout. Oops. You can copy/paste the ID fields to other layouts. The "name" fields have scripts attached to allow entry only in Find mode (version 7 makes this unnecessary).

You really also should have scripts to clear the "below" ID fields, if someone edits one "above" in the hierarchy; otherwise you could end up with a non-valid address.

I've set up Lookups, with the "use " (nothing) option, so that any valid lower choice will automatically fill in the upper fields. If you don't want this just switch the Lookup option back to "do not copy".

It is a hierarchy; so if you enter a valid Ward, it can automatically know all the other IDs.

It cannot however, wipe out the lower choices if you change an upper choice unless you write a script to do it.

RegionsZones.zip

[n1k0]

Here is a tutorial for working relational value lists (with downloadable sample):

http://www.afilemakeraffliction.com/list/howto/afa1207.html

Don't know what happens with digging down into multiple value-lists, but it might be of some help.

Peace!

Niko