13 Replies Latest reply on Jul 29, 2009 7:06 AM by comment_1

PROBLEMS!!

PROBLEMS!!

Post

I have following tables: OWNER --- SERIES --- NUMBERS

I have tried to relate OWNER to SERIES and NUMBERS to series, but it doesn't seem to work out for me the way I want...

I would like one unique NUMBER to be linked to one OWNER (one OWNER can though have many NUMBERS, but not the same as a other OWNER) in one SERIES.

Example SERIES 1: has NUMBER 1,3,7 and 8 linked to OWNER "Scott". The rest of the NUMBERS could be linked to the other people in the SERIES 1, but it has to be NUMBERS that I can "choose" for each OWNER and the NUMBERS can be anything from 1 to 1000 (you could almost say randomly NUMBERS). In SERIES 2: It could be just as SERIES 1 (SCOTT has 1,3,7 and 8) but it all depends on what NUMBERS I choose for the OWNERs at this SERIES, it's almost like a lottery with different days or weeks..

I have tried with portals, self-joining tables and calling the support in Sweeden (they just said it was to advanced for them)... I can't figure out a solution..

• 1. Re: PROBLEMS!!
If I understand correctly a Series has many numbers and each number can have at most one owner?

Series -< Numbers >- Owners

If so, it should be quite simple: In the Numbers table, define fields for SeriesID and OwnerID, and make your two relationships:

Series:: SeriesID = Numbers:: SeriesID

Owners:: OwnerID = Numbers:: OwnerID

Note:
In the Series table, SeriesID is a number field with auto-entered serial number. In the numbers table, SeriesID is just a number field. Similarly, In the Owners table, OwnerID is a number field with auto-entered serial number, and in the Numbers table it's just a number field.

• 2. Re: PROBLEMS!!

If I understand correctly a Series has many numbers and each number can have at most one owner?

YES! That is correct! SERIES 1 (for example) can only have one NUMBER assigned to one OWNER =)

Series -< Numbers >- Owners

If so, it should be quite simple: In the Numbers table, define fields for SeriesID and OwnerID, and make your two relationships:

Series:: SeriesID = Numbers:: SeriesID

Owners:: OwnerID = Numbers:: OwnerID

Note:
In the Series table, SeriesID is a number field with auto-entered serial number. In the numbers table, SeriesID is just a number field. Similarly, In the Owners table, OwnerID is a number field with auto-entered serial number, and in the Numbers table it's just a number field.

BUT... How do I avoid that one OWNER in SERIES 1 (for example) gets two eaqual NUMBERS, or that two or thre persons gets the same NUMBER in SERIES 1?!

Do you have any solution for that?

THANX! - for the answer =)

• 3. Re: PROBLEMS!!

Perhaps I am mising something. The way I see it, the Numbers table looks something like this:

NumberID  SeriesID  OwnerID   Value
1         1                   1
2         1         1         2
3         1         1         3
4         1         2         4
5         1         2         5
6         2                   1
7         2         1         2
8         2         2         3
9         2         1         4
10        2         2         5
11        3                   1
12        3                   2
13        3         1         3
14        3         2         4
15        3                   5
16        4         1         1
17        4                   2
18        4         2         3
19        4         1         4
20        4                   5

So there's no way the same number (regardless of series) can be assigned to more than one owner, and - assuming each series contains unique values only - one cannot get duplicate values in the same series.

• 4. Re: PROBLEMS!!

Perhaps I am mising something. The way I see it, the Numbers table looks something like this:

NumberID  SeriesID  OwnerID   Value
1         1                   1
2         1         1         2
3         1         1         3
4         1         2         4
5         1         2         5
6         2                   1
7         2         1         2
8         2         2         3
9         2         1         4
10        2         2         5
11        3                   1
12        3                   2
13        3         1         3
14        3         2         4
15        3                   5
16        4         1         1
17        4                   2
18        4         2         3
19        4         1         4
20        4                   5

So there's no way the same number (regardless of series) can be assigned to more than one owner, and - assuming each series contains unique values only - one cannot get duplicate values in the same series.

It's totally OK if the NumberID comes in order (1,2,3,4,5...).. But what I really want is the NumberID to come in disorder (the "lotteryway" or randomly). But I will myself choose the randomnumbers, I don't need any script for it.

Maybe I have to use a NumberID (with autonr.) and add another Number-field beneath it to accomplish that? And add som relations... ?

Thanx! This far =)

• 5. Re: PROBLEMS!!

The NumberID field should be an auto-entered serial number. The actual "number" here is in the Value field.

If you will be choosing the values yourself or randomly, you must make sure the values are unique within their series. For this, you should do two things:

1. Do not choose the same number twice in the same series. You could check for this by entering the selected SeriesID and the proposed value in global fields, and testing for a match among existing records. If no match is found, create the actual record.

2. Define a text field in the Numbers table, with auto-entered calculation (replacing existing data) =

SeriesID & "|" & Value

and set the field's validation to unique, validate always.

• 6. Re: PROBLEMS!!

The NumberID field should be an auto-entered serial number. The actual "number" here is in the Value field.

Is the Value field (I know there is a Value-list) an actual field in the NUMBER-table below the NumberID-field (auto-entered), or is it something else?

Could I make a portal that showed all the duplicate NUMBERS, with the corresponding OWNERS, in the specific SERIES?

I could then just delete the duplicate NUMBERS manually. Because I know there will not be many duplicates in one SERIES. This would be perfect for me...

• 7. Re: PROBLEMS!!

pune99 wrote:

Is the Value field (I know there is a Value-list) an actual field in the NUMBER-table

Yes. You can name it Number, if you prefer - or anything else.

pune99 wrote:
Could I make a portal that showed all the duplicate NUMBERS, with the corresponding OWNERS, in the specific SERIES?

Not exactly. You could easily make a portal that shows the duplicates of the currently viewed number. If you want to see all duplicates in a series, it's better just to find them: go into Find mode and enter the ID of the series you are interested in into the SeriesID field, and ! (exclamation mark) into the text field that concatenates SeriesID and Value, then perform find.

• 8. Re: PROBLEMS!!

pune99 wrote:
Could I make a portal that showed all the duplicate NUMBERS, with the corresponding OWNERS, in the specific SERIES?

Not exactly. You could easily make a portal that shows the duplicates of the currently viewed number. If you want to see all duplicates in a series, it's better just to find them: go into Find mode and enter the ID of the series you are interested in into the SeriesID field, and ! (exclamation mark) into the text field that concatenates SeriesID and Value, then perform find.

Is it possible to duplicate the NUMBER window and put it in FIND-mode?

That way I could put them by side of each other and immediately see if there is some duplicates in the other window?

Message Edited by pune99 on 07-28-2009 12:08 AM

• 9. Re: PROBLEMS!!

I am afraid I don't quite follow what you mean.

Perhaps, if you want to quickly spot which numbers have duplicates, you should define a self-join of the Numbers table:

Numbers:: SeriesID = Numbers 2:: SeriesID
AND
Numbers:: Value = Numbers 2:: Value
AND
Numbers:: NumberID ≠ Numbers 2:: NumberID

Then place the NumberID field from Numbers 2 on a list layout of Numbers, and format it to show "DUP" when non-zero (Format > Number… > Format as Boolean).

• 10. Re: PROBLEMS!!

Something like that =)

Should all the fields in NUMBERS be related to all the other fields in the self-join table. Like SeriesID directly related to SeriesID, Value directly related to Value and NumberID directly related to NumberID? .. I mean in the graph where you draw the relation strings between the tables and to the fields..

• 11. Re: PROBLEMS!!

pune99 wrote:
Should all the fields in NUMBERS be related to all the other fields in the self-join table

No, not all - only the three I have indicated. Note that the last pair (NumberID) uses the ≠ (NOT equal) operator. This is to make sure a record is not related to itself.

• 12. Re: PROBLEMS!!

Maybe I have not been so good at explaining what I want:

I am trying to make a series (series 1, series 2, series 3 and so on...). Every series should be sortet by years 2009, 2010, 2011 and so on..

In one series (for example series 1) there shall be one unique number (this number can be any number from 1-1000 or more) attached to one owner... One number (37 belongs to Stig for example) for one owner, in other words... "One owner though can have many numbers in the same series, but not the same number as another owner in the same series"

I must not have any duplicate numbers in one series. But every series has it's own "life". This means Series 1 can be exactly like series 2. The only difference here is that they have difference series numbers (series 1, series 2 and so on...)!

I must have the oppurtunity to see which numbers where attached to which owner in which series afterwards...

ooooch... hope you understand :/

How would the entity here look like?

• 13. Re: PROBLEMS!!

pune99 wrote:

Maybe I have not been so good at explaining what I want

Possibly - but I don't see anything in what you said now that's different from what I understood earlier.