I have a text file that is a list of cross-referenced index terms generated by a program that has ordered the terms hierarchically -like the list below (finance and accounting is a broader term of management accounting which is a broader term of finance - which has narrower terms of corporate finances, [etc ...] public finance. Public finance itself has narrower terms, and some of these terms also have even narrower terms).

All the index terms are set out in this way through the list (so they are repeated in different ways).

FINANCE

BT1: MANAGEMENT ACCOUNTING

BT2: FINANCE AND ACCOUNTING

NT1: CORPORATE FINANCES

NT1: CORPORATE TAXATION

NT1: FINANCIAL ANALYSIS

NT1: FINANCIAL CONTROL

NT1: FINANCIAL ENGINEERING

NT1: FINANCIAL FLEXIBILITY

NT1: FINANCIAL FORECASTING

NT1: FINANCIAL INFORMATION

NT1: FINANCIAL PERFORMANCE

NT1: PROJECT FINANCE

NT1: PUBLIC FINANCE

NT2: GOVERNMENT BORROWING

NT2: MONEY SUPPLY

NT2: NATIONAL DEBT

NT2: PUBLIC REVENUE

NT3: NON TAX REVENUE

NT3: TAXES

NT4: CAPITAL GAINS TAX

FINANCIAL MODELLING

BT1: FINANCIAL REPORTING

BT2: FINANCE AND ACCOUNTING

FINANCIAL PERFORMANCE

BT1: FINANCE

BT2: MANAGEMENT ACCOUNTING

BT3: FINANCE AND ACCOUNTING

I have imported this text file into a single table in FileMaker, each record has just the one field with one of the lines (including the tab spaces before NT: BT: etc, e.g.

NT2: GOVERNMENT BORROWING

I would like to recreate the hierarchical structure of the original program.

What is the best (and simplest - I'm only just beginning to use FileMaker) way of doing this? One table? Lots of tables (one for each NT and BT level)? Join tables?

How do I create the hierarchy so that, for example from the above list, capital gains tax is a narrower index term of taxes, which is a narrower term of public revenue , which is a narrower term of public finance? There are several thousand index terms so I don't want to do this manually.

Many thanks in advance if you can help with this

Is that the entire list?

No, he said thousands. Looks a little difficult to me as I don't know how you could automatically decide which are the subheadings, given the numbering convention shown.

Ah. Well, it's going to take some work. But I am not sure I fully understand the logic here:

I see that FINANCIAL PERFORMANCE has FINANCE as "broader term", and FINANCE has FINANCIAL PERFORMANCE as a "narrower term". If that's the overall rule, one could discard the BT lines, and build the links solely from the NT lines.

Moreover, if the NT2 lines signify grandchildren, one could ignore these as well and consider only NT1 lines for building the links.

Another important question is whether there are any topics that have more than one BT1 line. If not, the structure can be much simpler.

Edited July 7, 201015 yr by Guest

I see that FINANCIAL PERFORMANCE has FINANCE as "broader term", and FINANCE has FINANCIAL PERFORMANCE as a "narrower term". If that's the overall rule, one could discard the BT lines, and build the links solely from the NT lines.

Yes, that is the rule: each index term occurs once, alone, on a line in the text file (and in one record in the FM file) and below it is shown its broader terms (if any) and its narrow terms (and their narrow terms) (if any). All these broader/narrower terms preceded by BT/NT respectively. If the index term is itself a narrower/broader term for another then it will be shown again in the list. So one index term may appear several times within the text list - preceded by BTx or NTx as appropriate.

Moreover, if the NT2 lines signify grandchildren,

Yes narrower terms of NT1 term on line above.

whether there are any topics that have more than one BT1 line.

No index term has more than one BT1. Nor is there more than one BT2, BT3, BT4, etc for any terms.

Ok, then let's talk about what you need to get to: you can discard all lines other than (a) lines with no prefix and (: BT1 lines:

Next you need to turn this result:

FINANCE

BT1: MANAGEMENT ACCOUNTING

FINANCIAL MODELLING

BT1: FINANCIAL REPORTING

FINANCIAL PERFORMANCE

BT1: FINANCE

into:

FINANCEMANAGEMENT ACCOUNTING

FINANCIAL MODELLINGFINANCIAL REPORTING

FINANCIAL PERFORMANCEFINANCE

This would be easy to do in a text editor by replacing "¶BT1: " with a tab character, but could also be scripted in Filemaker.

Now you can import this into a Topics table with two fields: Topic and ParentTopic, and define two self-join relationships:

ParentTopic -< Topics -< ChildTopics

Finally, it would be advisable to give each topic a unique ID and base the relationships on TopicID instead of actual name.

ok - will try this. Will probably have to ask for more help when I've done (or failed to accomplish) that.

Thanks for the start...