12 Replies Latest reply on Jan 29, 2015 11:34 PM by erolst

    Special Relation/Query


      Hi Folks,


      I'd like to solve the following problem:

      There is a table "element" and a table "problem" with a 1:n relationship (element can have multiple problems). In a problem report I'd like to show all problems grouped by element. So far all works well but elements with no problem do not show up in report (obvious because there is no problem reported). So I'm creating for each element a "no problem" entry in the problem table, now all elects show up in a report.

      I think there must be a much more efficient way to solve this, because I'm creating thousands of "no problem" entries? Any suggestions?




        • 1. Re: Special Relation/Query

          You could auto-enter your foreign key in problems to the "no element" record, that way it's auto-entered to that null selection until someone selects a valid record.


          Else, you could calculate an if/else value in your problems table so that you can group by that instead.

          • 2. Re: Special Relation/Query

            Dear Mike,


            I do not understand what do you mean by "...auto-enter your foreign key in problems to the "no element" record...". There is always an element in the elements table ("master")  for an entry in the problems table ("detail") and the foreign key is on the problems-table.


            I'm thinking about having one (1) special entry in the problems table with the value of "no problem" and a primary-key of "0" and defining the relation between element and problem like "element.PK = problem.FK xor problem.FK = 0" ?!?




            • 3. Re: Special Relation/Query

              Mike's suggestion is a good one, except a Problem record must be created first.

              Ralf, do you create a New Element record via button & script or by using the File/New Record command?

              If via button & script then you can add a script to create your Problem record and auto-enter your foreign key as Mike describes.

              • 4. Re: Special Relation/Query

                when you define a field, you can set to have it auto-enter a value.



                • 5. Re: Special Relation/Query

                  Dear Mike,


                  thank you for the feedback.

                  I'm aware about the auto-enter mechanism, but I didn't get your logic. I think I wasn't able to explain my problem exactly.




                  "Element 1" --> one entry in table problem "a problem of element 1" (FK is referencing ID of element 1

                  "Element 2" --> NO entry in table problem

                  "Element 3" --> NO entry in table problem


                  Report should look like:


                  Element 1

                    a problem of element 1


                  Element 2

                    no problem


                  Element 3

                    no problem



                  According your suggestion there should be a record in the problems table "no problem" and the auto-enter calculation of the foreign key should look like what?

                  • 6. Re: Special Relation/Query

                    It sound like you're creating reports from the problem table.  Is there some reason why reporting cannot be done from the elements table?  The report layout could use a long portal (with many more rows than you'd expect to have for any one element).  Then mark all body part elements to slide up and to reduce the size of the enclosing part.


                    This layout design wouldn't be recommended for browsing as a list.  (Who'd want to scroll through all those huge portals?)  But, if your reporting process is scripted, this design pattern could report one element record for each element in the found set, and all of that element's problems.  If no problems have been associated with an element, then the element information would still be reported on and that record's problem portal would simply not display anything.


                    Would this work?


                    EDIT: by "report" I'm meaning a previewed and/or printed report.

                    • 7. Re: Special Relation/Query



                      Instead of "NO entry in table problem":

                      "Element 2" --> NO entry in table problem

                      Just auto enter "no problem" in the Problem Table Records. This would then be your default until a problem of element 2 is entered.

                      It seems to me the issue is in creating the first Problem Record (with an auto entered "no problem") when a New Element record is created. This can be done via script attached to a button or to be run when File/New Record is chosen.

                      Make sense?

                      • 8. Re: Special Relation/Query

                        Is this report supposed to be printed, or for viewing on-screen?

                        • 9. Re: Special Relation/Query



                          Your script to create a related Problem record could include something like this:


                          Set Variable [$ID ; Element::_PK_Element Record ID]
                          Go to Layout [Problems]
                          new Record/Request
                          Set field [Problems::_FK_Element Record ; $ID ]

                          Set field [Problems::problem; "no problem]     <------ or auto-enter "no problem" when a new record is created

                          Go to Layout [original layout]

                          • 10. Re: Special Relation/Query



                            The technique you describe will work as long as the portal has enough rows to hold all the problem records. I have done this in limited rare occasions. Much better to report from the Problems Table. This way there can be an unlimited number of Problems for each Element.


                            Just another 2 cents worth!



                            • 11. Re: Special Relation/Query

                              The report is supposed to be printed.

                              • 12. Re: Special Relation/Query

                                ralf_geyer@mac.com wrote:

                                The report is supposed to be printed.

                                So create a layout based on an Elements TO with the same design as the Problem report, find all Elements without a problem and print them; then go to the other table and print the original report (i.e. Elements with a problem).