Problem with portals

[LLX]

i have the most bizarre bug with my database.

its a 2 table database for a magazine (subscribers details and issues sent out)

and for some reason a random grouping of them are not relating to the subcriber table.

See attached

the sentID is the id of the enty in the issues table and as you can see theirs no consistency i have late entries in the 1000 range, 900, 600, heck even 30s and 17 range going up blank

Company id is the id from the subscriber table and even thought the id is valid its not loading the subscriber record.

All the other field are identical to fields in working records and yet...

[LaRetta]

We need a bit more information. It appears that there may be problem with indexing. If you can zip and post the file (even if empty clone - File > Save Copy As > Clone no records) it will help us get to the bottom of the problem.

[LLX]

i don't see how a clone will help since some records are showing up and some aren't (even with the same company ID) and i cant release our private database for trouble shooting a relationship problem.

i mean don't you need the records intact to figure out whats going wrong?

[LaRetta]

For all I know, your relationship is based upon a calculated ID on the parent side or the data type was changed or your solution is corrupt. I asked only to see your structure; nothing more. You gave no information from which to work. And no, I don't need to see the data necessarily; I mentioned indexing. But with NO information, I will not continue to guess.

The reason your post has not been addressed before this was because it doesn't provide enough information.

[LLX]

ok, sorry about that, clone is attached. I just didn't want to create a file that wouldn't be of any help.

Thank you for your time and any insight this may provide.

Subcribers_Clone.fp7.zip

[LaRetta]

What is the purpose of the Error calculation in the relationship? You can't look to Issues Sent table for information to hold in the Errors calculation when the Errors calculation itself is used in the key.

I can find no logic in what you are trying to do. That doesn't mean it doesn't exist; only that it isn't working the way you have it and I don't understand your intent here.

UPDATE: Oh, I see it isn't the Error calc but another field called Errors. But what is its purpose? Does it contain data on the Records side for those missing records?

Edited September 5, 2008 by Guest
Added update

[LaRetta]

BTW, why are you using GetRepetition() in that Error calc when there are no repetitions? We'll help you figure it out; hang in there. :wink2:

Edited September 5, 2008 by Guest

[LaRetta]

The more I explore your sample, the more concerned I become on its structure. You have an Issue Name with calculation:

Case (Issue = 1 AND Volume = 1 ; "Holiday 2005" ; Issue = 2 AND Volume = 1 ; "March/April 2006" ; Issue = 3 AND Volume = 1 ; "June 2006" ; Issue = 4 AND Volume = 1 ; "August 2006" ; Issue = 5 AND Volume = 1 ; "October 2006" ; Issue = 6 AND Volume = 1 ; "Holiday/December 2006" ; Issue = 7 AND Volume = 1 ; "February 2007" ; Issue = 8 AND Volume = 1 ; "April 2007" ; Issue = 9 AND Volume = 1 ; "June 2007" ; Issue = 10 AND Volume = 1 ; "August 2007" ; Issue = 11 AND Volume = 1 ; "October 2007" ; Issue = 12 AND Volume = 1 ; "Holiday 2007" ; Issue = 13 AND Volume = 1 ; "February 2008" ; Issue = 14 AND Volume = 1 ; "April 2008" ; Issue = 15 AND Volume = 1 ; "June 2008" ; Issue = 16 AND Volume = 1 ; "August 2008" ; "..." )

This indicates that you should have another table called Issues and probably another table called Volume (although you can cheat in this case and probably just have two fields (Issue and Volume) in one table. But your cSplit calc is also concerning and I see you painting yourself into a corner.

[LLX]

Errors (in issue sent) is set to either 0 or 1 and represents basically a "error" in sending that issue

when calculating total issues sent (Not to be confused with the total issues in a subscription) the idea was to add up the values in the "sent" field, and subtract the errors

so error calculation tabulates all errors for that subscriber with a value of 1 (at least that was my logic)

Basically every subscription has these values

Total issues for that term ie 6 issues a year

Duration of the term ie 12 months

issues per months ie .5 for 1 issue every 2 months.

Then there are a few cacluations based on that

Total sent

Errors (which deduct from the sent total)

and remaining for the term

[LLX]

The more I explore your sample, the more concerned I become on its structure. You have an Issue Name with calculation:

Case (Issue = 1 AND Volume = 1 ; "Holiday 2005" ; Issue = 2 AND Volume = 1 ; "March/April 2006" ; Issue = 3 AND Volume = 1 ; "June 2006" ; Issue = 4 AND Volume = 1 ; "August 2006" ; Issue = 5 AND Volume = 1 ; "October 2006" ; Issue = 6 AND Volume = 1 ; "Holiday/December 2006" ; Issue = 7 AND Volume = 1 ; "February 2007" ; Issue = 8 AND Volume = 1 ; "April 2007" ; Issue = 9 AND Volume = 1 ; "June 2007" ; Issue = 10 AND Volume = 1 ; "August 2007" ; Issue = 11 AND Volume = 1 ; "October 2007" ; Issue = 12 AND Volume = 1 ; "Holiday 2007" ; Issue = 13 AND Volume = 1 ; "February 2008" ; Issue = 14 AND Volume = 1 ; "April 2008" ; Issue = 15 AND Volume = 1 ; "June 2008" ; Issue = 16 AND Volume = 1 ; "August 2008" ; "..." )

This indicates that you should have another table called Issues and probably another table called Volume (although you can cheat in this case and probably just have two fields (Issue and Volume) in one table. But your cSplit calc is also concerning and I see you painting yourself into a corner.

there is lies the problem of a legacy system, weve been tweaking and building on the same database we used back in 2007 when we got filemaker.

your probably right we should probably have a table for the issues data as opposed to a caculation but that's less inportant then getting all data to show up which is the problem the portal is having now.

[LaRetta]

Oh, hey, I've worked with those types of pre-me systems. I understand completely.

... another field called Errors. But what is its purpose? Does it contain data on the Records side for those missing records?

Unless you put a value of either 1 or 0 in the Errors field in every record in the Records table, it can't display in Issues Sent. I would assume that first record has something in that field but the others don't?

So I would suspect that the problem isn't the ID but rather Errors. Also, Errors in the Records table is number. It is text on the Issues Sent side. It should be the same data type.

[LLX]

i thought about that but if you check my sceen shot the blank entries all read 0 or 1

[LaRetta]

Your screen shot only shows two fields which come from the Records table (cUniqeName and CompanyID) far left. There is a Errors field in Records (used in the relationship) and that is not displayed in your screen shot.

There must be a value in EVERY Records::Errors field of 0 or 1. The relationship you are using will not work otherwise!

I also mentioned other things in my post ... did you try correcting the data type mismatch?

[LLX]

just chanced error to a number field

no change

and yes every single field in the issues sent DB has data with the exception of Sent on, (some are blank there)

but Blank Sent On are in working entries as well as not working ones.

[LaRetta]

and yes every single field in the issues sent DB has data

Either I'm missing the point here or you are ... I keep asking about the RECORDS table - the one related to issues; not the Issues Sent table. Check the Errors field in that table. :wink2:

[LLX]

but the only errors field in Records is Errors Cacluation which just counts the errors in the related issues list

if you mean the entry in the database "errors" thats a legacy field which was a manual calculation it only exists in the manager db not in any live data (atleast not intentionally)

Edited September 5, 2008 by Guest

[LaRetta]

The relationship between the two files is invalid if any records in Records table don't have a value of 1 or 0 in Errors field! Look in your graph. Records::Errors is included in the relationship and is related on = to Issues Sent::Error.

That is why you are not getting CompanyID and cUniqueName to display in your table in Issues Sent.

UPDATE: No, there are two Errors fields. The one used in the relationship is Errors - not a calculation at all...

Edited September 5, 2008 by Guest

[LLX]

yeah i see that now, i had totally forgotten about the legacy field that had been replaced by relationship based calacuations. i removed it fro mthe relationship graph, deleted the legacy fields and updated the calucations liek remainin to be based on the other caculations not the legacy fields and it fixed everything

Thanks!

It's always the little thing that mess you up isn't it? :)

[LLX]

i created a 3rd table for the issue name. Hope you don't mind but i feel that this similar calculation field could be streamline but i'm not sure how and thought you might have ideas since its similar to the issues name field

Case (Type = "Basic Subscriber" and Country = "USA"; "USA 1 Year - 6 Issue Term" ;Type = "Basic Subscriber" and Country = "Canada"; "CAN 1 Year - 6 Issue Term" ;Type = "Ezine Subscriber" and Country = "USA"; "Ezine 1 Year - 6 Issue Term" ; Type = "Digital Subscriber" and Country = "USA"; "Digital 1 Year - 6 Issue Term" ;Type = "Single Ezine Issue" and Country = "USA"; "Online Single Issue" ; Type = "Single Digital Issue" and Country = "USA"; "Online Single Issue" ; Type = "Single Issue" and Country = "USA"; "Single Issue" ;Type = "Free Subscription" and Country = "USA"; "Complementary Subcription" ;Type = "Basic Subscriber" and Country = "United Kingdom"; "UK 1 Year - 6 Issue Term" ; "..." )