Concatenation of Related hierarchical Fields

[Wickerman]

Hi -- I'm trying something new for me, which is building up the html code for a fairly simple web-page of theater listings from data held in a 3-table relational hierarchy: STATE > CITY > THEATER. The page will simply have one a major State heading, then Sub-headings for Cities in each state, and then several Theaters listed under the cities.

I have the 3 tables set up 1-to-many top to bottom, and have created a calculation in the Theater table concatenating all the html Code and theater data in a "Theater_HTML" field. I have a similar field on the City and State tables containing the code to create those headings. I've used Global fields to hold the uniform chunks of html codes used in the calculations, including a block for 'Head' and 'Tail'. My goal is to generate a single document of code that will look like:

HEAD CODE

STATE A CODE

CITY A1 Code

Theater A1.1 Code

Theater A1.2 Code

CITY A2 Code

Theater A2.1 Code

STATE B Code

CITY B1 Code

etc

etc

END CODE

Q1) Now, I need to be able to Select which Theaters in the database are to be included for generating any given web-page as, for instance, by a field on the Theater Table Indicating say, "Small, Medium or Large" -- if I'm creating a web-page of only 'Large' Theaters, I *don't* want any State or City headings that have no theater listings. So, I'm figuring I create a Field in the State Table matching the one in the Theater table and Make a Table Occurence with a relationship defined on matching that field, -- so if I want to generate the 'Large Theater' page I'll select 'Large' on the State-level field so only Related 'Large' theater records will be captured - - - but do I need to include the 'CITY' table somehow in this Table-Occurence group in order to pull in that data? How do I define the relationship with the City Table in the way of the matching fields in State and Theater? Or am in the wrong context to begin with -- maybe I generate everything from the Theater table after doing a simple Find on the Large theaters?

Q2) Is more general (and maybe can be answered first) -- what calculations or script techniques do I use to build the full HTML code? I figure I'd start in the STATE context, and run a routine that goes to the first state (Table occurrences set to sort alphabetically), checks for Large theaters, and finding some, pastes in the State and City html blocks and then the City code and then chugs along untl all the related cities are done -- but I'm afraid I don't know how to begin! In fact, I' not sure whether I'm supposed to be doing this sort of thing via a Script or as a Calulation!

Or do I just do it as a Report? That seems the easiest way of generating this sort of hierarchical listing -- but does that allow me to treat the resulting text as a single string of data that I can paste into an html document? Or should I be somehow exporting the code *as* html to begin with?

I need help developing some concept here!

Any help appreciated.

Albert

[comment]

I'd suggest you look at the "xml example.fp7" file that came with your application.

[Wickerman]

Thanks for the pointer -- I'm loking at it, but I must admit I'm not conversant enough to understand how what I'm seeing works, or how it relates to my scenario -- I see it's a simple file with only one table -- is there documentation that explains this .xml example?

Is the implication that I'm going about this completely wrong and need to learn xml to do this sort of thing?

If possible, I'd really like to learn how to solve this inthe terms escribe because of the underlying concepts which is seems will arise in similar non-html things Im likely to run into . . .

Albert

[comment]

The .xsl stylesheet (found in the Export folder) is commented - though if you're just starting with XSLT, this would not be a good place to begin. But yes about the implication...

I am not sure what other applications you have in mind. I would try and avoid this if possible, but if necessary, you can find the records in the most atomic table (Theaters, in your example) and loop through them to build your report. For the headers, you can use something like:

...

Loop

If [ $category ≠ Table::Category ]

# DO HEADER

Set Variable [ $category ; Table::Category ]

End If

# DO BODY

Go to Record [ Next ; Exit after last ]

End Loop

...