1 2 Previous Next 19 Replies Latest reply on Nov 21, 2016 4:42 AM by Stigge

    Help needed - Relation issue


      Hello friends.


      Am a bit stuck. :/ and hope someone know an easy solution for me =)


      I have a running Asset management system up and running. PC`s mainly.


      Scenario before:

      Our Asset manager get a ticket of a user needing a PC, she then takes the users ALPSID and put it in to the PC to allocate that PC for the user.

      Works fine.


      Scenario now:

      Out Asset manager get a ticket from 2 different places, old place just like scenario 1. New place dont have ALPSID instead they use another ID named SSO.


      Now to the problem, how do i implement this in my system ? I have added all users SSO in the Staff Table. so its there, i can also view it.

      But when it comes to the ALPSID i have that field on my PC table so i can just change the value and it changes user. i need that function from SSO field aswell.

      The problem is that all other information about the user like name, e-mail, costcenter, department etc is in relation with the ALPSID.


      Am guessing i need to create a new relation between my PC table and Staff table with the key SSO instead, but then i also need to create a new set of the other fields aswell. with mabye a hide/show of ALPSID beeing empty or not ?? or what do you guys suggest me doing ?



      PS: sorry for the bad english and problebly hard to read question =D hope you understand my question anyway =)

        • 1. Re: Help needed - Relation issue

          Is it possible to change the "AND" to an "OR" ???


          • 2. Re: Help needed - Relation issue

            Hi Stigge,


            FileMaker has "stored calculated fields" which you could use for this. (There are other options as well, like using SQL to look up the user)


            But going the stored calculated field route, you can make a "compound" key field.  Simply do


            PersonalKey = LIST ( Personal Reg::ALPS ID ; Personal Reg::GE SSO )  this wil put the alps id and the ge sso id on two seperate lines.  In older version of FileMaker you need to do Personal Reg::ALPS ID & "¶" & Personal Reg::GE SSO.


            Make sure the calculation result is text and that storage is set to stored.  This field will then have both keys seperated by a ¶ and that is actually your OR.


            In the RegIT table make a field called PersonalKey


            Then set the relationship to

            PersonalKey = PersonalKey



            Happy Coding

            • 3. Re: Help needed - Relation issue

              Ahaaaa.. smart...


              But how does that work when the asset manager for example change the owner of an asset. she then for example set the field "SSO" with a new SSO value. does it then know to change the other related fields ? or do i need to do something so it does ?

              And how do i show the values of PersonalKey, do i create 2 fields and calculation on them with getValue(PersonalKey;1) for example ?


              Sorry for all the noob questions =) just want to make sure i know what am going before i start =D

              • 4. Re: Help needed - Relation issue
                Johan Hedman

                If you want to have a stored calculation you can either

                     Use a Script Trigger that changes value after a field have been changed

                     Have a FileMaker Server Scheduled Script run say every 30 minutes to re calculated fields

                     Have a stored calculation, but using that in a relationship is not going to give you a fast solution (relationship portal)

                • 5. Re: Help needed - Relation issue

                  The "stored" vs "unstored" only means when does FileMaker calculate the value.  If a calculation is stored it will change the values if one the fields in that calculation changes.  If it is unstored it will calculate the field whenever the field is needed (which is slow but uses less space)


                  Be sure to set the entire field to a calcuation though, not the auto-enter option.


                  In the past we used to have huge fields like this for example

                  left(name;1) & ¶ &

                  left(name;2) & ¶ &

                  left(name;3) & ¶ &

                  Leftword(name;1) & ¶ &

                  Middlewords(name ; 2; 1)



                  It works very fast actually! (just uses more space)  Cause stored fields don't need to be recalculated only when actually changing a name, or key in your case.)


                  If in Personal Reg SSO is changed the Calculated key will be updated. And when you change a value in RegIT it will relookup.


                  You can use the SSO and ALPS fields as usual in the PersonalReg table.  You could use GetValue as you suggested as well.

                  • 6. Re: Help needed - Relation issue



                    But how does it really works then with the key.. PersonalKey


                    lets say an asset has the following:

                    PersonalKey (1111111; 45454545)

                    ALPSID = "1111111"

                    SSO = "45454545"


                    And the Asset Manager change the SSO to "2222222" doesnt that then look for the relationship with

                    the PersonalKey (1111111;2222222) and that dont exist, since its 2 different users.. ? or does it then ignore the "1111111" ? or do i manually need to script so it removes the "1111111" ?

                    • 7. Re: Help needed - Relation issue

                      I suggest that you carefully consider using neither value as your key in the relationship. Use an ID that uniquely identifies either the user or their computer that is neither externally supplied value but an internally generated ID value such as a serial number or the text returned by Get ( UUID ).


                      This then simplifies your relationship and also avoids issues that can arise if someone accidentally enters the wrong data as the ALPID or SSO and the error is not immediately detected--which can lead to problems where correcting the error disconnects related records.

                      • 8. Re: Help needed - Relation issue

                        i agree.. but since we are adding users from a table of 2500 users its kind of hard to know what ID they have. we do know what SSO or ALPSID they. And by adding another step with users search will only make the procedure slower i think :/


                        i might be wrong though.

                        • 9. Re: Help needed - Relation issue

                          Well one option (and there are many to this question :-))  is to have in the layout where the user enters a SOS or ALPSID, then with a script trigger, perform an executeSQL statement that select the "new" id field and use that in a relationship. 


                          Other is in the layout just have one field with the label SSO/ALPID and with the calculated field in the source database, either will work.  Point is we used that trick for years, before SQL came out and filtered portals etc.   It's one of typical filemaker things (compound key fields) and if the calculation is stored it won't have noticeable impact on speed only a bit more storage space.

                          • 10. Re: Help needed - Relation issue

                            Well isn't an asset only assigned to one person?  So it would never have and an SSO and an ALPSID?  In the asset table it is either SSO OR ALPSID.  You need the calculated key in the Personel Table.


                            You can use a calculated field in the asset table as well, just make sure that when SSO is filled ALPSID is erased and vice versa.

                            • 11. Re: Help needed - Relation issue

                              Have fun with this :-)


                              Happy Coding!

                              • 12. Re: Help needed - Relation issue

                                Yes, but you can easily use either a name or one of these two ID values to find the appropriate user record quite easily and does not require an additional data entry step to do so.

                                • 13. Re: Help needed - Relation issue

                                  to your question about ALPSID and SSO, every user have both. but depending on what ticket system we are recieving the ticket in its based on one of them and the other isnt showing. thats the problem.

                                  So in my user info on each asset i need both to be showing. so the problem am having is to build fields that makes it possible to change one of them ALPSID or SSO and it updates information on the rest of the fields about the user.



                                  User have:

                                  ALPSID = 10

                                  SSO = 32

                                  Name = Carl



                                  this asset is to be used by another user with SSO 20. So when i change the SSO to 20 rest of the fields updates with the user info for example: alpsID = 4, Name = Stefan. same happends if i instead use ALPSID to change.


                                  The way am testing now is to create User fields in the asset table. as global. and triggers on exit of the fields ALPSID and SSO. but am having some problems to get to work properly and am not sure its the best way to do it.

                                  • 14. Re: Help needed - Relation issue

                                    Can one asset be used by more than one user?

                                    1 2 Previous Next