11 Replies Latest reply on Jul 4, 2015 3:45 AM by hoib

    Another Gem of a problem



      Another Gem of a problem


      Well, here goes.  This is a long one, sorry.  On FM14 Pro Adv and Windows 7.

      I have a TO of DASHBOARD and a TO of PREFERENCES.  This solution uses data separation.  The DASHBOARD TO is part of the UI data segment and PREFERENCES is part of the "Data" data segment.  The UI portion is small with only three TOs - Graphics, Virtual Lists and DASHBOARD.  The Data segment is large and filled with 25 TOs including PREFERENCES.  In Data, each TO has many fields.  We are using Anchor-Buoy throughout.

      I have what I call "QuikTips" Table in Data which I want to accept, hold and preserve text and date values in designated fields and show them in the DASHBOARD when the user first enters that opening DASHBOARD layout.  I don't want them editing it.  I just want the office staff to edit QuikTips over in the PREFERENCES" layout.  I've cloned a tabbed interface in both spots containing the QuikTip text and a date of entry.

      It is hard to draw or define a relationship from PREFENECES <> DASHBOARD.  I've tried setting up __kf_QukTip_id in DASHBOARD with a constant value auto-entered as a value of "1" with a corresponding __kp_QuikTip_id in PREFERENCES also set to constant value of "1".  Both are text fields and both TOs and keyfields show in the "=" in the Relationship Diag on the UI segment.  I figured that's going to link the two.  Wrong.  I cannot get DASHBOARD TO to show up as a Related Field when attempting to place the corresponding field on the PREFERENCES tab designated for the Office Staff to use to edit.  Note that PREFERENCES is tabbed itself and shows other preferences settings screens beside the one I'm dealing with,.

      So what's this tell me?  The awful truth that I keep reading about is that the PREFERENCES layout is based on the PREFERENCES TO.  Thus any tabs within that layout are destined to be sourced only from the PREFERENCES TO.  And thus they are not available to DASHBOARD.

      Should I be trying to Set Fields into our already existing GLOBAL_TEMP_FIELDS as each field is input on the PREFERENCES side so that they can be retrieved (copied) into separate (and by the way totally redundant) fields that I cook up on the DASHBOARD side?  Like this? 

      Set Field [GLOBAL TEMPFIELDS::splash_text-box_#1; QuikTips::new_splash_textbox_#1] as long as [GLOBAL TEMPFIELDS] can look back at PREFERENCES layout.  And then when DASHBOARD is up, open script has Set Field [DASHBOARD:QuikTips:splash_text_box_#1]; GLOBAL TEMPSFIELDS::splash_text_box_#1.  Is that the proper technique? 

      This really screams for a correctly configured relationship that goes from Prefs over to Dashboard but it won't go.  I've tried relating from Prefs to Global TempFields and then from Global TempFields to Dashboard.  There's just something wrong with my thinking.  Plus it's using redundant fields in both TOs so it's gotta be wrong.

      This is really confusing me...  I hope I've done a good job of explaining this. 


        • 1. Re: Another Gem of a problem

          I can't tell from your description which relationships and TOs are in which file. TO's and relationships in the data file are not accessible from the UI file. THey are mainly needed for any calculation fields that access data from a related record. Other than that they aren't much use.

          So you need to focus on the relationships defined in your UI file.

          So if you plan on creating and editing records from the Quick Tips table on the preferences layout, it would be simplest to link a TO of QuickTips to Preferences and your use of the value 1 will do the job to link any record in preferences to all records in QuickTips. You can also use the X operator instead of = and then the values in the fields don't matter, but since you probably want to use "allow creation of records via this relationship", the constant value 1 method is slightly better.

          Then if you also want to display the same QuickTips on your Dashboard layout, you simply need to add a different TO of QuickTips and link it to Dashboard. Either a Cartesian Join (X operator) or the same set up that you've defined using the value 1 in both match fields may be used here as well.

          The trick is to use TWO TO's of your QuickTips table and then be careful to refer to the correct TO from the context of the two layouts.

          • 2. Re: Another Gem of a problem

            I can't tell from your description which relationships and TOs are in which file.

            PREFERENCES TO lives in the Data segment.  DASHBOARD TO lives in the UI segment.  QUIKTIPS TO lives in the Data segment.  I have removed all relationships in TOs for PREFERENCES, DASHBOARD and QUIKTIPS that I had created in the Data segment since those don't seem to be as important as I thought and because, well, they don't work, as you've aptly pointed out. 

            I'm working on getting this relationship thing accomplished.  There's a lot of mistakes I have to take down.  I'll post back with results.



            • 3. Re: Another Gem of a problem


              As I stated previously, the only TOs that matter for this issue are the ones you set up in the UI file. Any that you set up in the Data file will not have any effect on how your layouts function in the UI file.

              • 4. Re: Another Gem of a problem

                And, I thought that they did.  Data Separation Model - Something learned.

                Now, here's what I have gotten done today - off and on.

                First I cleaned up a lot mess that I had made.

                On the PREFERENCES layout, in the 2d tab called Sessions,, I placed another tab control - 5 tabs across the top. Because I now have the relationship pointed correctly from QUIKTIPS ---PREFERENCES I now have QuikTips fields showing and available as related fields in the FM menu.  Ii never was able to get to this point before.  Wow that was easy but it sure took me a long time to understand it.  Anyway....

                I placed the 2 needed fields on each of the 5 tab control panels.  Those fields are splash_text_box_#1 through #5 and splash_text_box #1...#5_Date_Entered again to show from the source QUIKTIPS table.  In Browse mode, they're there, they don't show "Unrelated Table".  Then they didn't work. 

                So then I looked harder at the relationship - the key to all keys.  I tried a Cartesian join.  Poof!  Working sweet.  Copied the tab control over to the Dashboard.  Modified the fields because I now had QuikTips available to me in Dashboard (as dashboard_QUIKTIPS in the rel diagram), Did some resizing and it all works fine now!

                Whew!  What a marathon!  But a lot of learning and re-steering went on.

                Thank you Mr. Phil.  I should put your name on this somewhere in the credits.


                • 5. Re: Another Gem of a problem

                  Those fields are splash_text_box_#1 through #5 and splash_text_box #1...#5_Date_Entered

                  That sounds like you might be better off with a table of 5 records in place of a single record with 5 sets of fields...

                  • 6. Re: Another Gem of a problem

                    OK, I believe I do have just as you say.

                    The QUIKTIPS table has these fields: 


                    Plus the Primary Key:  __kp_QuikTips_ID  (which I set in Validation to calc value of "1"

                    I believe this is what you are saying.  Why the "=" sign in the rel diag details would not work is a mystery.  Because I have the __kf_QuikTips_ID sitting in PREFERENCES with a constant value of "1".  As soon as I invoked the Cartesian join, it immediately started working,.  Odd....


                    • 7. Re: Another Gem of a problem

                      I am saying that:

                      You need one field named spash_text_box with a second field named splash_text_box_dateentered.

                      Then define a match field. I wouldn't label it "__kp..." because it doesn't meet the definition of a primary key. The main requirement for a primary key is that the value be unique in the table were the primary key is defined. A field with an auto enter field option (not a "validation") does not meet that requirement.

                      That's it in terms of fields.

                      To get second pair of text_box and DateEntered fields, you add another related record to the table instead of adding another pair of fields to the same record. This allows you to have any number of quick tip entries--making for a more flexible means of managing this data.

                      Please recall that I said that you might be better off doing this. The small number of quick tips presumed by your design make this a minor issue.

                      • 8. Re: Another Gem of a problem

                        Scratch this post.  It's all coming from a WRONG set of expectations...  And I mistakenly said "Validation" when it actually is "Auto-Enter".

                        Oh!  I am beginning to see!  That's why the "=" won't work.  All records have an ID of "1".  Finally,....  However...

                        This passage has me puzzled though: 

                        To get second pair of text_box and DateEntered fields, you add another related record to the table instead of adding another pair of fields to the same record.

                        ... add another related record:  From where?   And I don't see where I am "adding another pair of fields to the same record" unless my method of using the "1" to match is flawed. (which we seem to agree now isn't perfect).  Each record in QIKTIPS is comprised of Text_Box and DateEntered has to have it's unique ID, which I don't have because they're all set to "1".

                        And then:  ...related record to the table:  Which table?  The three tables are:  PREFERENCES, QUIKTIPS AND DASHBOARD.  Which of these is the specific table? PREFERENCES & DASHBOARD each have layouts attached to them.  QUIKTIPS does not.  Another way I like to think of it is:  QUIKTIPS gives me a way to "bank-shot" from PREFERENCES (where the data called text_box and text_box_DateEntered) are input by the office staff.  DASHBOARD is where the session volunteers read the day's quiktips for current information.

                        I don't understand why or where I need another related record?  I guess I have to study this a bit more.



                        • 9. Re: Another Gem of a problem

                          I must be really dense!  I really got off on the wrong foot with this as I thought I had it covered.  But, NO I do not!  Gradually, it is making a bit more sense.  The basic set up is wrong.  See screenshot.

                          To illustrate the point, I went to the Data segment and looked at QUIKTIPS in table view.  It finally dawned on me this is not set up the way I had thought it would be.  I was looking for one record each for each of my Text_Box and DateEntered couplets.  That's not what I got at all!!!  Notice too that the __kp_QuikTips_ID is empty when I was expecting a "1" to be there.  No wonder the "=" sign wasn't working. 

                          I need to go back now and rethink this and carefully read what Phil has said, now that I've discovered the problem and am trying to analyze it.  It kind of embarrasses me no end because this is simple File > Record > Field stuff.



                          • 10. Re: Another Gem of a problem

                            The value of 1 should work just fine. It's just not a primary key. A primary key isn't what you want here. I was just pointing out that your naming convention was not correct for how the field is defined. That doesn't keep it from working but it can confuse people when they look at the design of your database solution.

                            If you have this relationship:


                            Preferences::constOne = QuickTips::One

                            where constOne is a calculation field with the number 1 as it's sole term to make sure that constOne always stores the constant value 1. (This can be more reliable than an auto-entered 1.)

                            Then you can enable "allow creation...." for QuickTips in the above relationship and a portal to QuickTips placed on your Preferences layout can be used to add as Many QuickTips records as you need by entering data into the bottom blank "add" row of your portal.

                            • 11. Re: Another Gem of a problem

                              OK, I'm trying to learn this technique.  I think I like it better than mine because I can add more Text_Box Items and be able to handle this more effectively.

                              First of all, I have kept the __kp_ and __kf_ naming convention just for now just so I could try to work on the single match key (=) idea.  I promise to modify it later so as not to confuse the next person.  I totally get that.  I did want to have an easy way of putting everything back together in case of failure.

                              In QuikTips, I've created just two fields.  QT_Text_Box and QT_Text_Box_DateEntered.  I take it these will be "replicated" as we add new QT items.  OK, good!    And so unfortunately the portal in PREFS shows no QT_Text_Box and QT_Text_Box_DateEntered fields.  The __kp_ and __kf_ match fields are set to number and are set to a constant value of "1" using Calculated Result.  Went and tried quotes around the 1 in each as well as taking the quotes away leaving just a 1.   Then I asked, is the "1" actually being stored and will it show from either context?  I put the __kf_ on the Preferences layout in Data, browse and it's blank, not a "1".  I thought the "1" would show.  I shifted over to the UI, put the __kf_ on that layout, again, it's blank.  The "1" is nowhere to be found.  I did the same for the __kp_ in the QuikTips TO.  It's blank and not a "1" like I thought.  If I put an absolute numeric value in the __kf_ and __kp_ fields, why do they not display?

                              So, it's working but not without the Cartesian and the indiv fields (#1, #2 and so forth).  And of course with the Cartesian, the options to "Create new records" is greyed out.  Again, I am trying to work in UI, though I do have to touch the Data segment.

                              So, please give me a shove here.  (and not off the cliff...)