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

    PROBLEMS!!

    pune99

      Title

      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..   

       

      Please, help?! 

        • 1. Re: PROBLEMS!!
          comment_1
             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!!
            pune99
              

            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!!
              comment_1
                

              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!!
                pune99
                  

                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!!
                  comment_1
                    

                  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!!
                    pune99
                      

                    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!!
                      comment_1
                        

                      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
                          


                        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!!
                          comment_1
                            

                          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!!
                            pune99
                              

                            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!!
                              comment_1
                                

                              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!!
                                pune99
                                  

                                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!!
                                  comment_1
                                    

                                  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.