Big project--little knowledge

[dolphy]

[WARNING--LONG POST]

Help!!! I'm a newbie with a big project who's building his first database while poring over Coulumbre & Price.

The main purposes of this database are to:

1. generate e-mail, phone & snail mail lists of contacts subdivided by the following categories:

Members, Contacts, Reporters, Editors, Photoeditors, Donors, Organizations, News Media.

2. Show current member status

3. display dues, special contributions & payments for services per given time period.

4. display group members by committee, skills, and activities.

These are the main tables involved, along with their fields (most foreign keys omitted in an attempt, however feeble, at brevity)

Contacts:

Contact ID, Contact Type, Member, Organizational contact, Editor, Reporter, Photo Editor, Donor, Other, First Name, Last Name, Street Address 1, Street Address 2, City, State, Zip, Department, Position, Home Phone, Work Phone, Other Phone, Cell Phone, e-mail, fax, Website (URL), Cong. Dist., City Council, State Assembly, State Senate, Election district, Community Board District, Organization Affiliation, send snail mail? Y/N, send e-mail? Y/N, Active-Inactive?, mailing labels

Members: (child of Contacts parent)

Member ID, Contact Info (via Portal from Contacts table), Date Membership Started, Current Membership Status, Receive Calls, mailing labels, Activities (popup list), Skills (popup list), interests (popup list), Committees (via portal from Committees table), Committee Attendance (haven't figured out how to set this up yet)

Events:

Event ID, Event Name, Event Date, Location, Amount, Committee(s) (via Portal from Committees table), Activities, Event Type

Contributions (child of Events table):

Contrib ID, Contact info (via portal from Contacts table), Date, Amount, Event, mailing labels, phone list, Type (popup list): Annual dues, Special contribution, Payment for service/event

Organizations (child of Contacts table):

Organization ID, Org Name, Type (from popup list), Contact ID (fk), Contact Info (via portal from Contacts table), Notes, mailing labels, lookup table

Committees (child of Members table):

Committee ID, Committee Name (from popup list), Committee Roster (via portal from Members table), Member Attendance

News Media (child of Contacts table):

News Media ID, Type, Organization ID (fk), Org Name, Contact ID (fk), Contact info (via portal), Mode of contact (popup list):, snail mail, e-mail, phone, fax, Posting deadline, Circulation (numbers), Media type, Language, Contacts for Articles:, Editor, phone number, Editor

[Ugo DI LUCA]

Hi,

Very shortly, what you are looking for seems to be a Join file at first where you could see the Comittee Attendance.

a) Would be solved by Conditional Value Lists

Either Filtering Portal or the Go to Related Record Script step could solve this.

c) Once you've got the above to work, you'll discover that Finds could be done through your relationships, as well as the sorts.

So, make a search here for these keywords and you'd have plenty to read for the whole summertime :

Conditional Value List

Line Item or Join File

Many to Many

Filtering Portal

Filter

Go to Related Record or GTRR

Now, you'd also like to check if some other users didn't posted something that is close to your needs.

So try "Attendance" or "Membership" also.

Good Luck.

It was a good thing that you've spent some time in the explanation. The settings seem clear and clean for what I can see. May be too much fields in the Contact File...

[dolphy]

Ugo

[dolphy]

Ugo

[Ugo DI LUCA]

Try the following.

Create File A and File B

In both files, create 3 fields:

1. c_constant (indexed, 1)

2. serial (number, autoentered, unmodificable)

3. A text field, where you'd input some text...

4. A number field, where you'd input some number...

Create quickly 20 records in each file.

In File A, define a relationship that you'd call FileAToFileBbyConstant

In File B, define a relationship that you'd call FileBToFileAbyConstant

Draw a portal in File A using this relationship, and drop inside the number field and the text field from File B using this same relationship. Don't place the serial field in this portal.

Do the same in File B.

See, you've got a list of the 20 records from File A in the portal in File B...and the reverse is also true.

Now, add global fields (number*) g_ExternalKeyB in File A and g_ExternalKeyA in File B.

Go in scriptmaker in File A and create a script

[Ugo DI LUCA]

Sorry, a few detail missings and an error...

Both relationships created have c_constant field as a match on any side...

and

"Now go back in Layout Mode, in the layout where your portal is, and as a start, select the [color:"red"] NUMBER field. Go in define button, and define the field to be a button with script "setExternalKey". "

[dolphy]

Ugo--

Choosing the "number" field instead of the "serial" field enabled g_ExternalKey to work. Thanks!

I'm confused by the statement "Both relationships created have c_constant field as a match on any side." Please elaborate.

I created a field via drag & drop, associated it with the relationship FileAToFileBByConstant, & selected ::serial as you instructed. Now I've got two fields in File A named "serial"

[Ugo DI LUCA]

The relationships should be established from File A to File B using for both sides the c_constant key, either for FileAToFileBbyConstant and FileBToFileAByConstant.

I think you solved this one, as you said g_externalkay was working.

About the "Related" serial...

In order to see the serial you grabbed from the portal row, you should create a new relationship.

FileAToFileBByg_External Key using g_ExternalKey at left side and serial for Right side.

Change the relationship for this "Related" serial field.

Now, draw a rectangle the size of your portal row, make it transparent, and put it in the portal row. Remove the first script from the Number field, and attach it to this rectangle. Now, you've got a button row.

Sorry about this.

It should work now.

[dolphy]

Ugo

[Ugo DI LUCA]

dolphy said:

I changed the serial field to a text field to be sure that the data was really being grabbed. Since both g_ExternalKeyB & serial were giving me the serial number, I needed to be able to distinguish between them.

Uhh ?? You need these keys to be of same format.

I need to figure out how to set up my Committees table such that:

[dolphy]

Ugo

[Ugo DI LUCA]

Hi Dolphy,

Not necessarily. But in the example I gave you the g_ExternalKey was grabbing the external serial number. So it should adopt the same format (number).

In a real situation, you'd better switch from serials to auto-entered text calculations.

There could be some reason to "hide" this key, and some others to show . It depends upon what key you grabbed and what kind of information this key could offer to the user. A Customer Account_ID could be shown or not, an Invoice_ID should be shown, but a Unique Data Key should be hided (as it doesn't offer any informaton to the user).

[dolphy]

Ugo--

Je comprend maintenant.

Merci beaucoup!

dolphy