Relational layout

[skiingpatty]

Hi,

 

I have been using relational layouts - but on a very limited basis.

I now want to use them a little more like they were intended..but can't figure out what to even ask for in the help menu.

 

A. 60 year anniversary is coming up for a concert. In the program we want to list all the conductors from all the years, and what years they conducted.

B. All the years are in the main record - so there are 60 records.

C. Related to each year - by year - is all the quest conductors who have come over the years... it can be several.

D. If I look at the year .. 1960 - in the main layout - I see 5 conductors in their portal.

 

E. But I can't seem to do that in reverse... meaning. One record for the conductor --- with all the years in a portal??? ..

 

I hope that makes sense.. any help is greatly appreciated!

Patty

[AHunter3]

You would not normally have all the years in a portal from conductors since each conductor record is related to only one year.

 

If the same conductor performed in 1986, 1989, 1993 and 2004, what you need is three tables: Years, Conductors, and ConductorYears. In Years, you have 60 records (one for each year, as you say). In Conductors, each Conductor has one and only one record. The Years file has a portal to ConductorYears, and the field for Conductor in that portal is a dropdown value list drawing its values from the Conductors file.

 

What that lets you do is create another relationship, between Conductors and ConductorYears, so that for any Conductor you can put up a portal to all the years that that Conductor was on the podium.

 

Now, if you mean that you want a portal to all the years (why? for navigational purposes?), that's a different story. You just create a new relationship to Years and use the dropdown widget in the Define Relationship dialogue to make it a "universal" relationship (so that the Conductor record is related to all the records in Years). Then the portal will show all 60 years.

[skiingpatty]

So ...

 

If what I need is three tables: Years, Conductors, and ConductorYears.

----- I have a table for Years - which is the original table, and has 60 records.

------I have a table for Conductors, but there are 250 records...and I don't know how many Conductors.

 

If the same conductor performed in 1986, 1989, 1993 and 2004, In the Conductor table, he has 4 records (for each year) .. I know that is wrong, but I donn't know how to correct it.

 

How do I get each conductor to have only one record? Is it too late? .. The info was all put in a flat file .. I split it up, and created everything from the original table - Years. So, each year had it's conductor listed. The conductor, therefore was repeated in other years.

?

[AHunter3]

Not too late. Change name of Conductors table to ConductorsYear. Create new Conductors table, with a text field Conductor and at least one other field (you may have a slew of fields that describe each Conductor — home symphony, email address, name of cat, whatever... but you do need at least one, and for now we'll say City).

 

Create a relationship between ConductorYear (which is your original Conductor table) and Conductor, based on Conductor Name. Check the checkbox that says "allow creation of records through this relationship" on both sides.

 

Then make and perform a looping script that loops through all the 250 records you've got, setting Conductor::City to ConductorYear::City for each record. What that does is create a new record in Conductor if there isn't already one there, whereas if one's already there it just sets City to the local value of City. If you do indeed have a lot of other fields pertaining to the Conductor (as opposed to pertaining to the Conductor's engagement in 2003 or whenever), set those in your looping script as well.

 

When you're done, you should have a record for each conductor in Conductors, and now you can make a nice layout in Conductors showing all the info fields about that person and with a portal to ConductorYears showling all the years that that person was on the podium.

[skiingpatty]

smiley-frown I guess I am missing something --- big time.

 

Not too late. Change name of Conductors table to ConductorsYear.

------ I changed the name of GuestConductors to GuestConductorsYear

 

Create new Conductors table

-------I created a new table called GuestConductors

 

with a text field Conductor and at least one other field

-------I gave it a text field for First Name, Last Name, Middle Name, Title.

(and made a layout with the fields)

 

Create a relationship between ConductorYear (which is your original Conductor table) and Conductor

-------I created a relationship between GuestConductorsYear and

GuestConductors - linking between the two - First Name and Last Name. Clicked on the equals sign, and checked the box - allow creation of records through this relationship on both sides.

 

Then make and perform a looping script that loops through all the 250 records you've got...

-------I don't know what that means. To try to figure it out .. I went into Scripts - new/create.

2. Only saw Apple Script where I could 'calculate"

3. Checked original Apple Script

4. In the Calculation field - I chose the table and field

GuestConductorsYear::FirstName to GuestConductors::FirstName

 

*** it stops and says it can't find the field

FirstName to GuestConductors::FirstName

 

***** Plus, I am not sure what you meant by ---" for each record "

 

????? There is another relationship question... if I have the dates in a portal -- how do I do a find, or create a calculation to find, the most recent date? .... Meaning... You came to visit on 10-1-06 - and again on 11-1-06. How do I find and lock-in-on 11-1-06 to merge with the date on the letter that said you came to visit.

 

?? Thanks for your help. I get so far, then I hit a wall. But, the examples in the book (all of them) are so vague or for programers -- they just say stuff like... For any date : ... then .. ... Result --1,2

At least, that is what it sounds like to me. There never seems to be an explaination of WHAT is being done.

You guys are a god-send. Thanks for helping out!!!

patty

[AHunter3]

Then make and perform a looping script that loops through all the 250 records you've got...

-------I don't know what that means. To try to figure it out .. I went into Scripts - new/create.

2. Only saw Apple Script where I could 'calculate"

 

No, no, not AppleScript. AppleScript is wonderful but you don't need it here.

 

A looping script consists of script steps like this:

 

Loop

..Exit Loop If []

..Do Things []

..Go to Next Record [Exit After Last, maybe: if you can do it that way, you don't need "Exit Loop if". Looping through a found set allows you to quit the loop after the last record, way convenient]

End Loop

 

 

 

In your case, you can just loop through the found set. The 250 records that you've got.

 

Go to Record [First]

Loop

..

..Go to Record [Next, Exit After Last]

End Loop

[skiingpatty]

I guess I better quit .. I just deleted all the guest conductors ..

 

I don't understand "where" you "do something" in scripts.

I tried this .. and promptly deleted all the guests.

 

Go to Record/Requests/Page [first]

Loop

Set Field [guestconductorsyear::firstname; guestconductors::first name]

Set Field (etc.. last name, middle, title)

Go To Record/Request/Page [next;exit after last]

end loop.

 

I guess set field means erase. (

[AHunter3]

:eek:

 

I meant to do the Set Field setting values in the brand new (empty) table. The part on the right (the value to set the field to) would point to the existing table, the one I had you rename.

 

Loop

..Set Field [GuestConductors::First Name, GuestConductorsYear::First Name]

..Go to Record [Next, Exit After Last]

End Loop

 

I do hope you did a backup so you didn't lose all your data!