Company type issue part 2

9 messages

Company type issue part 2

Reply Threaded

Hi
I had a company type value issue about 3 months ago that was successfully rectified.

I have a database with a report in it that wasn't sorting correctly because the contacts in the db had multiple company types.

It would only show the first company type in the value list.

This was corrected by creating a separate table called "company type"

The relationship was contactID (in main press list) to contactID (in companyType table). The field in company type was called "companyType"

I created a portal in the main press list layout and ws able to add multiple company types (press, national, TV, etc).

This helped my report layout because now in the layout, I setup to show records from the company type table, sorted the subsummary by company type and used the relationship between the 2 tables to bring in the other fields in the layout (first name, last name, company,address notes etc).

I have now created a separate related db called "coverage". This is related to the main press list (where the contacts reside) by field "contactIDMailing=contactIDMailing". This allows me to create coverage for many artists in one database without having to make multiple layouts in the mainpresslist for each artist, for which we have many.

Now, I want to sort the layout "report" by company type in the coverage db. The issue is, when I make the relationship the same between coverage and company type, it only shows the fist company type value that is related to the contact in the main press list. It is not sorting or showing multiple company types as it did in the main press list.

So to sum up my problem, I have contacts. These contacts have multiple company types. These contacts have multiple coverages. The coverages should show the coverage for each contact and also the company type for each contact.

Im trying to wrap my head around this as I believe its many to many.

Any suggestions would be halpful

Thank you

Lou
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

Steve Cassidy-3

Re: Company type issue part 2

Reply Threaded

Lou

I'm not really following you. To clarify, maybe you could mock up (in
an email) what you expect your report to show?

The result of your previous question was, I think, a report something
like this:

Press
Contact 1
Contact 3
Contact 4
TV
Contact 3
Contact 5
Contact 8
National
Contact 1
Contact 4
Contact 5
etc.

What do you want now?

Remember that a report (ie in preview mode or printed) may have
different requirements for setting it up than a layout for on-screen
viewing.

Steve

On 18 Jun 2009, at 05:43, Louis Ardolino wrote:

> So to sum up my problem, I have contacts. These contacts have
> multiple company types. These contacts have multiple coverages.
> The coverages should show the coverage for each contact and also
> the company type for each contact.
>
> Im trying to wrap my head around this as I believe its many to many.
>
> Any suggestions would be halpful

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

Louis Ardolino-2

Re: Company type issue part 2

Reply Threaded

Hi Steve

That is what I want the report to look like but the report resides now in the coverage database instead of the contact db.

Report Name: Care Bear (care bear is a field in coverage called project type)
Press - from companyType
Contact 1 - from main presslist
coverage info - from coverage
Contact 3- from main presslist
coverage info - from coverage
Contact 4- from main presslist
coverage info - from coverage
TV- from companyType
Contact 3- from main presslist
coverage info - from coverage
Contact 5- from main presslist
coverage info - from coverage
Contact 8- from main presslist
coverage info - from coverage
National- from companyType
Contact 1- from main presslist
coverage info - from coverage
Contact 4- from main presslist
coverage info - from coverage
Contact 5- from main presslist
coverage info - from coverage

On 6/18/09 5:43 AM, "Steve Cassidy" <[hidden email]> wrote:

Lou

I'm not really following you. To clarify, maybe you could mock up (in
an email) what you expect your report to show?

The result of your previous question was, I think, a report something
like this:

Press
Contact 1
Contact 3
Contact 4
TV
Contact 3
Contact 5
Contact 8
National
Contact 1
Contact 4
Contact 5
etc.

What do you want now?

Remember that a report (ie in preview mode or printed) may have
different requirements for setting it up than a layout for on-screen
viewing.

Steve

On 18 Jun 2009, at 05:43, Louis Ardolino wrote:

> So to sum up my problem, I have contacts. These contacts have
> multiple company types. These contacts have multiple coverages.
> The coverages should show the coverage for each contact and also
> the company type for each contact.
>
> Im trying to wrap my head around this as I believe its many to many.
>
> Any suggestions would be halpful

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

Steve Cassidy-3

Re: Company type issue part 2

Reply Threaded

Lou

Still not following you... What do you mean by 'coverage info'? Isn't
Care Bear the coverage info for this report?

Try again. Forget your actual file structure. Try to give an idea
what actual data you want to see -- some real-life values.

I have a vague feeling that your structure is incorrect, but it is
difficult to know without knowing what kind of data we are talking
about. Somehow, a field in 'Coverage' called 'project type' seems a
bit odd... I would expect a 'project type' field to be an attribute
of a table called 'Project' or something like that...

Steve

On 18 Jun 2009, at 13:45, Louis Ardolino wrote:

> That is what I want the report to look like but the report resides
> now in the coverage database instead of the contact db.
>
> Report Name: Care Bear (care bear is a field in coverage called
> project type)
> Press - from companyType
> Contact 1 - from main presslist
> coverage info - from coverage
> Contact 3- from main presslist
> coverage info - from coverage
> Contact 4- from main presslist
> coverage info - from coverage
> TV- from companyType
> Contact 3- from main presslist
> coverage info - from coverage
> Contact 5- from main presslist
> coverage info - from coverage
> Contact 8- from main presslist
> coverage info - from coverage
> National- from companyType
> Contact 1- from main presslist
> coverage info - from coverage
> Contact 4- from main presslist
> coverage info - from coverage
> Contact 5- from main presslist
> coverage info - from coverage

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

Louis Ardolino-2

Re: Company type issue part 2

Reply Threaded

OK

My report looks something like this:

CARE BEAR press tracking

NATIONAL
Augusta Chronicle
Lauren Noto Augusta, GA
Running: Mp3, Streaming audio, preview
Run Date: 6/17/09
Pitch Status: Likes the care bears, wishes she was 5 again so she would be embarrassed when co-workers catch her watching them on youTube.

WEB
Augusta Chronicle
Lauren Noto Augusta, GA
Running: Mp3, Streaming audio, preview
Run Date: 6/17/09
Pitch Status: Likes the care bears, wishes she was 5 again so she wouldn't be embarrassed when co-workers catch her watching them on youTube.

Indie Hits/IndyHits.com
Mike Galaxy Hollywood, CA
Running: Promotion, Streaming, MP3
Run Date: 6/17/09
Pitch Status: Hates care bears but is running it anyway.

MUSIC SUPERVISORS
Augusta Chronicle
Lauren Noto Augusta, GA
Running: Mp3, Streaming audio, preview
Run Date: 6/17/09
Pitch Status: Likes the care bears, wishes she was 5 again so she wouldn't be embarrassed when co-workers catch her watching them on youTube.

Indie Hits/IndyHits.com
Mike Galaxy Hollywood, CA
Running: Promotion, Streaming, MP3
Run Date: 6/17/09
Pitch Status: Hates care bears but is running it anyway.

Does this help:

As you can see, the contact can show up numerous times in the report.

The problem is, the report is now in the coverage database, not in the main press list database

I need to sort the report by multiple company types, thus I have created the company type table. Worked like a charm until I created the new database "coverage" in which I am now running the reports out of.

Creating reports for projects such as "care bears" in the main press list was a nightmare. You needed to create a tracking page, a report page, and fields for each project because it was essentially a flat database. Thus we have hundreds of fields all the projects: careBearsRunning, careBearsRun_date, careBearsPitch_status, etc. this would repeat for each new project we would take on.

The coverage database helped solve that but now we cannot sort by company type anymore.

I thank you for all your help.

Lou
On 6/18/09 9:29 AM, "Steve Cassidy" <[hidden email]> wrote:

Lou

Still not following you... What do you mean by 'coverage info'? Isn't
Care Bear the coverage info for this report?

Try again. Forget your actual file structure. Try to give an idea
what actual data you want to see -- some real-life values.

I have a vague feeling that your structure is incorrect, but it is
difficult to know without knowing what kind of data we are talking
about. Somehow, a field in 'Coverage' called 'project type' seems a
bit odd... I would expect a 'project type' field to be an attribute
of a table called 'Project' or something like that...

Steve

On 18 Jun 2009, at 13:45, Louis Ardolino wrote:

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

Steve Cassidy-3

Re: Company type issue part 2

Reply Threaded

On 18 Jun 2009, at 14:46, Louis Ardolino wrote:

> My report looks something like this:

OK Lou, now we are getting somewhere... But I think you may have some
work to do....

Without considering your structure at all, I'm going to look at what
things (entities?) you are dealing with here. Seems to me you have:

- Promotions (eg Care Bear)

- Press Contacts (eg Augusta Chronicle, Lauren Noto)

- Contact Types (eg National or Web)

- Contact Type/Contact Pairings (eg Augusta Chronicle - National)

- Coverage (eg Care Bear by Augusta Chronicle - National on 6/17/09)

> As you can see, the contact can show up numerous times in the report.

Yes. But only once per Coverage record. So it does seem correct that
you are running your report from Coverage...

> The problem is, the report is now in the coverage database, not in
> the main press list database

Which may or may not be the correct place to run the report. At the
moment, though, it feels right to me...

> I need to sort the report by multiple company types, thus I have
> created the company type table. Worked like a charm until I
> created the new database "coverage" in which I am now running the
> reports out of.

I think one problem you have is that you are mixing up Contacts with
an incidence of a Contact in Coverage. Another is that you have
perhaps not seen that Type is in fact a join file in a many-to-many
relationship between Contacts and types of contact. (In that each
Type can be associated with many contacts, while each contact can
have multiple types.)

Coverage means that a Promotion is run by a Contact but in the guise
of a particular Type (ie Augusta Chronicle - National). It seems to
me, then, that Coverage should have a relationship on the one hand to
Promotions (each Promotion has many Coverage records). Coverage
should also have a relationship to a particular Type record -- which
is in fact a unique pairing of a Contact and a contact type. This
would enable you to associate a Coverage record with, say, "Augusta
Chronicle, Music Supervisors".

So I see something like this:

Contact < Type < Coverage > Promotion

To get a report, you could find all relevant Coverage records (say
for a particular period). Sort by Promotion (to get the CARE BEAR
break), then by Type (to get the breakdown by contact type). Then you
can display the unique contact data by pulling it through the two
relationships from Contact.

You might want to consider some renaming. Look at this:

Contact < Contact-Type > Type

This means you have a table of Types (National, Web, etc.) and a join
table Contact-Type (in which each record holds two ids: a Contact id
and a Type id). Contact-Type then represents a unique pairing of
Contact and Type, and it is this that runs a promotion. I suspect
that, currently, the equivalent of Type in this scheme is hidden in a
hard-coded value list. And you call Contact-Type simply Type.

> Creating reports for projects such as "care bears" in the main
> press list was a nightmare. You needed to create a tracking page,
> a report page, and fields for each project because it was
> essentially a flat database. Thus we have hundreds of fields all
> the projects: careBearsRunning, careBearsRun_date,
> careBearsPitch_status, etc. this would repeat for each new project
> we would take on.
>
> The coverage database helped solve that but now we cannot sort by
> company type anymore.

Yes. One way to build a database is to go step by step -- but you
will end up rebuilding every now and again because the structure is
wrong. The other way is to clarify the full structure of your
workflow from the beginning -- harder but there's less rewriting
later! I think you are stuck with some rebuilding in order to get all
that you want...

I hope that might get you started on the right track. Write again if
you need more help.

Steve

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

Louis Ardolino-2

Re: Company type issue part 2

Reply Threaded

In reply to this post by Louis Ardolino-2

Steve,
Thanks so much. Very helpful. I'm going to respond to your comments in caps.
------
Without considering your structure at all, I'm going to look at what
things (entities?) you are dealing with here. Seems to me you have:

- Promotions (eg Care Bear)
THIS IS THE PROJECT NAME. (The projects actually reside in a separate db, called "projects")

Press Contacts (eg Augusta Chronicle, Lauren Noto)
CORRECT
- Contact Types (eg National or Web)
CORRECT (residing in a separate db )
- Contact Type/Contact Pairings (eg Augusta Chronicle - National)
CORRECT
- Coverage (eg Care Bear by Augusta Chronicle - National on 6/17/09)
COVERAGE WOULD BE: tv or booking (coverage_type field), sent on 12.12.2009 (music_sent field), running 12.30.2009 (run_date field), "likes the album, wants to do more" (notes field).

I think I am almost there.
I have to wrap my head around matching up the relationships.

If I create a table in "coverage" db, I should associate them by contactid=contactid?
Also, sorry if you explained this already, how would I set up the tables to relate in this type relationship?
Contact < Type < Coverage > Promotion
All by contactids?

Thanks for all your help

Lou
Louis Ardolino
Forrex Solutions
917.650.8467
Typos courtesy of RIM

----- Original Message -----
From: [hidden email] <[hidden email]>
To: [hidden email] <[hidden email]>
Sent: Thu Jun 18 11:04:14 2009
Subject: Re: Company type issue part 2

On 18 Jun 2009, at 14:46, Louis Ardolino wrote:

> My report looks something like this:

OK Lou, now we are getting somewhere... But I think you may have some
work to do....

Without considering your structure at all, I'm going to look at what
things (entities?) you are dealing with here. Seems to me you have:

- Promotions (eg Care Bear)

- Press Contacts (eg Augusta Chronicle, Lauren Noto)

- Contact Types (eg National or Web)

- Contact Type/Contact Pairings (eg Augusta Chronicle - National)

- Coverage (eg Care Bear by Augusta Chronicle - National on 6/17/09)

> As you can see, the contact can show up numerous times in the report.

Yes. But only once per Coverage record. So it does seem correct that
you are running your report from Coverage...

> The problem is, the report is now in the coverage database, not in
> the main press list database

Which may or may not be the correct place to run the report. At the
moment, though, it feels right to me...

> I need to sort the report by multiple company types, thus I have
> created the company type table. Worked like a charm until I
> created the new database "coverage" in which I am now running the
> reports out of.

I think one problem you have is that you are mixing up Contacts with
an incidence of a Contact in Coverage. Another is that you have
perhaps not seen that Type is in fact a join file in a many-to-many
relationship between Contacts and types of contact. (In that each
Type can be associated with many contacts, while each contact can
have multiple types.)

Coverage means that a Promotion is run by a Contact but in the guise
of a particular Type (ie Augusta Chronicle - National). It seems to
me, then, that Coverage should have a relationship on the one hand to
Promotions (each Promotion has many Coverage records). Coverage
should also have a relationship to a particular Type record -- which
is in fact a unique pairing of a Contact and a contact type. This
would enable you to associate a Coverage record with, say, "Augusta
Chronicle, Music Supervisors".

So I see something like this:

Contact < Type < Coverage > Promotion

To get a report, you could find all relevant Coverage records (say
for a particular period). Sort by Promotion (to get the CARE BEAR
break), then by Type (to get the breakdown by contact type). Then you
can display the unique contact data by pulling it through the two
relationships from Contact.

You might want to consider some renaming. Look at this:

Contact < Contact-Type > Type

This means you have a table of Types (National, Web, etc.) and a join
table Contact-Type (in which each record holds two ids: a Contact id
and a Type id). Contact-Type then represents a unique pairing of
Contact and Type, and it is this that runs a promotion. I suspect
that, currently, the equivalent of Type in this scheme is hidden in a
hard-coded value list. And you call Contact-Type simply Type.

Louis Ardolino-2

Re: Company type issue part 2

Reply Threaded

In reply to this post by Steve Cassidy-3

Hi Steve

Would it be possible to create a table in the coverage db called: companytype and run the report in that table?

On 6/18/09 11:04 AM, "Steve Cassidy" <[hidden email]> wrote:

On 18 Jun 2009, at 14:46, Louis Ardolino wrote:

> My report looks something like this:

OK Lou, now we are getting somewhere... But I think you may have some
work to do....

Without considering your structure at all, I'm going to look at what
things (entities?) you are dealing with here. Seems to me you have:

- Promotions (eg Care Bear)

- Press Contacts (eg Augusta Chronicle, Lauren Noto)

- Contact Types (eg National or Web)

- Contact Type/Contact Pairings (eg Augusta Chronicle - National)

- Coverage (eg Care Bear by Augusta Chronicle - National on 6/17/09)

> As you can see, the contact can show up numerous times in the report.

Yes. But only once per Coverage record. So it does seem correct that
you are running your report from Coverage...

> The problem is, the report is now in the coverage database, not in
> the main press list database

Which may or may not be the correct place to run the report. At the
moment, though, it feels right to me...

> I need to sort the report by multiple company types, thus I have
> created the company type table. Worked like a charm until I
> created the new database "coverage" in which I am now running the
> reports out of.

I think one problem you have is that you are mixing up Contacts with
an incidence of a Contact in Coverage. Another is that you have
perhaps not seen that Type is in fact a join file in a many-to-many
relationship between Contacts and types of contact. (In that each
Type can be associated with many contacts, while each contact can
have multiple types.)

Coverage means that a Promotion is run by a Contact but in the guise
of a particular Type (ie Augusta Chronicle - National). It seems to
me, then, that Coverage should have a relationship on the one hand to
Promotions (each Promotion has many Coverage records). Coverage
should also have a relationship to a particular Type record -- which
is in fact a unique pairing of a Contact and a contact type. This
would enable you to associate a Coverage record with, say, "Augusta
Chronicle, Music Supervisors".

So I see something like this:

Contact < Type < Coverage > Promotion

To get a report, you could find all relevant Coverage records (say
for a particular period). Sort by Promotion (to get the CARE BEAR
break), then by Type (to get the breakdown by contact type). Then you
can display the unique contact data by pulling it through the two
relationships from Contact.

You might want to consider some renaming. Look at this:

Contact < Contact-Type > Type

This means you have a table of Types (National, Web, etc.) and a join
table Contact-Type (in which each record holds two ids: a Contact id
and a Type id). Contact-Type then represents a unique pairing of
Contact and Type, and it is this that runs a promotion. I suspect
that, currently, the equivalent of Type in this scheme is hidden in a
hard-coded value list. And you call Contact-Type simply Type.

Steve Cassidy-3

Re: Company type issue part 2

Reply Threaded

Lou

I'm going to respond to two of your messages here. First, the earlier
one, where you asked:

> If I create a table in "coverage" db, I should associate them by
> contactid=contactid?
> Also, sorry if you explained this already, how would I set up the
> tables to relate in this type relationship?
> Contact < Type < Coverage > Promotion
> All by contactids?

No, most definitely NOT all by contactid!!

Files:

Contact (primary key field, contactid)

Promotion[Project] (primary key field, projectid)

Type[Contact_Type_Pair] (primary key field, contact_type_pairid)
contactid field for relationship to Contact
(and I would have a typeid field for a relationship to a table
listing the available types)

Coverage (primary key field, coverageid)
contact_type_pairid field for relationship to Type[Contact_Type_Pair]
projectid field for relationship to Promotion[Project]

This would be one way to do it. And as I said, you run the report in
Coverage like this:

Find all relevant Coverage records (say for a particular period or
whatever range your report is to contain). Sort by Promotion[Project]
(to get the CARE BEAR break), then by Type[Contact_Type_Pair] (to get
the breakdown by contact type). Then you can display the unique
contact data by pulling it through the two relationships from
Contact. The Promotion[Project] data comes through the relationship
from Coverage to Promotion[Project].

Sorry about the confusion of names. Unfortunately, you didn't give
your actual table names in the beginning and, in fact, some of your
table names are misleading.

Then you asked:

> Would it be possible to create a table in the coverage db called:
> companytype and run the report in that table?

Anything is 'possible'. But are you asking the right question?

I believe the report you want is best (most easily) developed in the
Coverage table as I outlined before. Sure, you could force another
table to produce it, but why bother?

I also note that you seem preoccupied with "databases" not tables. It
really doesn't matter which "database" (ie file) a table is in. The
important thing is the table and its relationships to other tables
(whether in the same database file or not).

HTH

Steve
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au