1 2 Previous Next 15 Replies Latest reply on May 18, 2016 2:53 AM by MarkBløndal

    Two portals - Same table but different "find request"

    MarkBløndal

      Hello all you very very smart developers.

       

      I feel like I must be missing something essential/simple?..

       

      I'm trying to gather some information on some servers as well as the cables connected:

      "Servers" is a list of servers with all the relevant information (IP, type, Serial nr. and so on)

      "Cables" is a list of RJ45 cables, and simply has a cable nr. plus where it originates and ends (Rack 01 Slot 01 - Shortened to RxxSxx)

       

      At the moment I've created a "dashboard" with a single record, here I'm trying to gather the server information and cable information.

      I've successfully gotten the server information in standard fields, and a portal with the cable information based on the starting point of the cable.

      But I can't figure out how to get a portal with the data matching the end point of the cable ??

       

      For clarification I've created a merged field with Rack and Slot to create an ID I can work with, so the server will have a location in the racks and that correlates to the starting point of all the cables. (RxxSxx - Both as server placement and cable ID)

       

      I'd really just like a solution where I:

      Get all CableID records

      WHERE Start or End = RxxSxx(from the server list)

       

      And of course present that in a portal, since the data can range from 1-48 based on type of server/unit..

        • 2. Re: Two portals - Same table but different "find request"
          MarkBløndal

          No it's simply not finding any records because I use the starting point of the cable as the primary key.

           

          Let me try and elaborate a little more:

          I have a server entry, let's call it ServerA and it's placed in Rack 02 Slot 01.

          Because of the placement of the server, it's now dubbed R02S01 - I then use that name to get cables that start at this point in the rack.

          In my test data it's cable 8 and 9 - But, I also need cables that start in Rack 1 and end in Rack 2..?

          Because I use RxxSxx as the primary key I can't get it to also display data in a portal from where the cable ends.

           

          I need two portals

          1. Based on the cable start point

          2. Based on the cable end point

           

          Well actually one portal that displays both set of data would also be the solution, I'm not picky I just need it to display both datasets.

           

          My whole issue is basically that we need to stay up to date with the cable routing in the server room, and for that I need the overview to be as dynamic as possible.

          So if I open ServerA and need to know what cables are connected to it, I need the information presentable and easy to edit.

          • 3. Re: Two portals - Same table but different "find request"
            doughemi

            Try a Termination table, linked to the Cable table by a CableID and the Server table by ServerID.  Each Termination record is one end of one cable. Do some error checking to make sure that each cable has only two termination records, and each server slot has only one termination record.

            • 4. Re: Two portals - Same table but different "find request"
              MarkBløndal

              Yet another good question from my part:

              What is a Termination Table?

              It's not a terminology I'm familiar with.

              • 5. Re: Two portals - Same table but different "find request"
                doughemi

                It is just the name of another table in your solution.  It acts as a join table between the Server and Cable tables. Join tables are used when there is a many-to-many relationship between entities. Instead of considering that a cable is identified by a "start" and "end", think of it as having two interchangeable (and otherwise indistinguishable) terminations. This simplifies the relationship between cables and servers.

                • 6. Re: Two portals - Same table but different "find request"
                  erolst

                  The idea is that rather than having a “monolithic”

                   

                  Cable (rackStart, rackEnd)

                   

                  you create

                   

                  Cable (id) --< Termination (id_cable, type, rack)

                   

                  where Termination::type is either "start" or "end" (and you ensure to always have exactly two termination records per Cable record).

                   

                  Now you can use

                   

                  Server::rack = Termination::rack / Termination::id_cable = Cable::id

                   

                  to see the cables that are connected to a server either way.

                   

                  A solution for your current setup would be to create these relationships:

                   

                  Server::rack = Cable_byStart::rackStart

                  Server::rack = Cable_byEnd::rackEnd

                   

                  create a calc field (text!) "cCableIDList" as

                   

                  List ( List ( Cable_byStart::id ) ; List ( Cable_byEnd::id ) )

                   

                  and use

                   

                  Server::cCableIDList = Cable::id

                   

                  to get a unified related set.

                  • 7. Re: Two portals - Same table but different "find request"
                    MarkBløndal

                    Hi Erolst

                     

                    So to obtain the cable ID, in my portal, I need double entries of every cable?

                     

                    Currently I've got:

                    StartRack, StartSlot and StartPort with matching EndRack, EndSlot and EndPort.

                    Two Calc fields (StartID, and EndID) that simply hold the combination of StartRack & StartSlot and EndRack & EndSlot.

                    I ignore port for the moment, but I could include it if need be since it's easy to add in a table as long as it can get at the information.

                     

                    But as I understand your suggestion, I should create a setup as follows (with dual entries for every cable):

                    CableNumber (We have numbered tags on the cables for easy management)

                    Type (Start or End)

                    Rack (What Rack it is placed in)

                    Slot (What slot it's placed in)

                    "RackSlotID" (As a combination of both Rack and Slot) ?

                     

                    With this setup, how do I pair it with a server ID that isn't related to anything in the cable table?

                    Should I check for "ServerPosition - RackSlotID" and then sort/filter based on type?

                     

                    So from ServerDashboard I pair my "RackName - RackSlotID".

                    Then I filter based on Start/End and sort by CableNumber.

                    Would that be a viable solution?

                     

                    I haven't played with List in FileMaker, what benefit is there for me in this scenario?

                     

                    Thank you for your help, I've been stumbling in the blind with this for a week or more..

                    • 8. Re: Two portals - Same table but different "find request"
                      erolst

                      MarkBløndal wrote:

                       

                      Hi Erolst

                      But as I understand your suggestion, I should create a setup as follows (with dual entries for every cable):

                      No, two entries in a new table that only holds data pertaining to a particular end of a cable.

                       

                      See the attached file for a slimmed-down version.

                       

                      btw, ignore my suggestion re your current setup; using a calculated field in the Cable table as a multi-line key is of course the better idea.

                      • 9. Re: Two portals - Same table but different "find request"
                        MarkBløndal

                        There doesn't seem to be an attached file in your post? EDIT: There is now..

                         

                        Besides the obvious manual labor, is there a reason for not simply creating a table with double entries?

                        I've practically created it already, since the amount of data isn't very high yet.

                        • 10. Re: Two portals - Same table but different "find request"
                          erolst

                          MarkBløndal wrote:

                          Besides the obvious manual labor, is there a reason for not simply creating a table with double entries?

                          Yes – which entry for a given cable is the “correct” one? If you change a cable attribute, you need to change it in two places, not just one … etc.

                          • 11. Re: Two portals - Same table but different "find request"
                            MarkBløndal

                            I have to admit I can't quite follow your logic in the sample file.

                            Cable has a Type (That's empty) but also references a Type from CableTermination?

                            And the generel structure of the db seems askew for some reason.

                             

                            The logic (in my head):

                            Server

                            - Location (Rack and Slot combined RxxSxx)

                            - A portal that shows CableNumber with same Location

                             

                            Cables

                            - Rack

                            - Slot

                            - Port

                            - Type

                            - CableNumber

                            - RackSlot-CalcField (Matches with Location field from Server)

                             

                            I can't seem to figure out how you would match the server location with the correct cables, without matching Location and RackSlot-CalcField?

                             

                            Once again, thank you very much for your patient help.

                            • 12. Re: Two portals - Same table but different "find request"
                              erolst

                              MarkBløndal wrote:

                               

                              I have to admit I can't quite follow your logic in the sample file.

                              Cable has a Type (That's empty) but also references a Type from CableTermination?

                              And the generel structure of the db seems askew for some reason.

                              Cable::type (just an attribute name I came up with) would be a different attribute from CableTermination::type …

                               

                              MarkBløndal wrote:

                              The logic (in my head):

                              Server

                              - Location (Rack and Slot combined RxxSxx)

                              - A portal that shows CableNumber with same Location

                               

                              Cables

                              - Rack

                              - Slot

                              - RackSlot-CalcField (Matches with Location field from Server)

                               

                              Put rack and slot into the CableTermination table; you don't need a calc field in either table, just use two predicates in your relationship

                               

                              Server::rack = CableTermination::rack

                              Server::slot = CableTermination::slot

                               

                              and as shown, a portal on a Server layout can “look through” CableTermination into Cable.

                              • 13. Re: Two portals - Same table but different "find request"
                                MarkBløndal

                                I think I've almost got it working on my sample file, the one mimicking yours..

                                 

                                But I've begun questioning why I need the CableTermination table?

                                 

                                Since CableTermination will have to hold:

                                CableNumer

                                Rack

                                Slot

                                Port

                                Color

                                Type

                                 

                                Which is exactly the same as the Cable Table would hold?

                                 

                                Also, when creating a cable the "Start" point gets the cable number, but the "End" part doesn't?

                                Do you have a spare minute to look at what I'm doing wrong?

                                It's easy if you look at the last two records in the Server table and the Cable with the label number 99..

                                • 14. Re: Two portals - Same table but different "find request"
                                  erolst

                                  MarkBløndal wrote:

                                  I've begun questioning why I need the CableTermination table?

                                   

                                  Since CableTermination will have to hold:

                                  CableNumer

                                  Rack

                                  Slot

                                  Port

                                  Color

                                  Type

                                   

                                  Which is exactly the same as the Cable Table would hold?

                                   

                                  Also, when creating a cable the "Start" point gets the cable number, but the "End" part doesn't?

                                   

                                  Neither does – it's an attribute of the cable. Simply follow these guidelines:

                                   

                                  A Cable (C) record holds all attributes that describe the cable as a whole – cable number, cable type(?), whatever else (e.g. purchase price, length, colour (?) )

                                   

                                  A CableTermination (CT) records holds all attributes that describe a cable termination point.

                                   

                                  In other words, these two entities have nothing in common except their primary/foreign key. Together a C record and its two related CT records (should) deliver a full description of a given cable and its current deployment, without any redundancies.

                                   

                                  btw, you had some orphaned records in TC. Set your C --< CT relationship to delete records in CT when a record in C is deleted; a termination without a cable doesn't make sense.

                                  1 2 Previous Next