10 Replies Latest reply on Jul 2, 2011 7:48 PM by wiz302pa

    IWP:  relational database

    wiz302pa

      Title

      IWP:  relational database

      Post

      I have 3 tables in a FM database file that are posted on a FM 3rd party hosting server.  I am having trouble with relational links.  Can relational databases be used in Instant Web Publishing?

        • 1. Re: IWP:  relational database
          philmodjunk

          Yes, they can be used. Can't say much more than that without knowing more about your database design.

          What version of FileMaker?

          Are your 3 tables all in one file?

          What problems are you encountering with the related tables? (Since web published systems don't exchange data with the server as frequently as a Filemaker Client, some things like conditional value lists may not update like you would expect if you where not using a web browser.)

          • 2. Re: IWP:  relational database
            wiz302pa

            Let me explain:

            I have 4 tables in one file, 3 of which are related.  See figure below.

            file:///Users/wiz302pa70/Desktop/Screen%20shot%202011-06-13%20at%2012.10.54%20PM.png

            The problem occurs only when users access it by Instant Web Publishing (IWP).  It works OK when using FM Pro 11 11.0v3.  

            The three tables are linked by the same variable (with different names in each table); the variable is a 5-digit (integer) ID number.  

            THE WAY THIS SHOULD WORK:  When the ID number is added to a field in the "Dispatch Log" table and it loads 4 other fields in the DL table using data from the "Volunteer Database" table.  These fields are:  Last Name, First Name, Phone number #1 and Phone #2.

            THE PROBLEM:  When in IWP (on line), with an ID number keyed onto the ID field, the user selects "submit", and in a few seconds the updated record is returned for viewing.  The ID number is where it should be.  But, the 4 fields (first and last name and 2 phone numbers) are blank (no data).  And, in the VD table the values for the 4 fields have been cleared (blank, no data).  If, at that point, the 4 cleared values are keyed back into the VD table, they display correctly in the DL table.  For some reason the relational link to refer VD data to the DL is working, BUT in the process of keying in the ID number and "submitting" via IWP, the 4 fields on the VD (source) table are cleared.

            The problem is 100% consistent in IWP, and always happens every time an ID number is added (and submitted) to the DL field.  [If the same ID number is used in more than one DL record, they still remain blank.]

            FYI:  We are using 888.net to post the FM file for online access via IWP.  This problem does not occur when using the same file (without IWP) in FM Pro 11.

            • 3. Re: IWP:  relational database
              wiz302pa

              Trouble with link to figure in the above reply -use this link

              http://web.me.com/wiz302pa70/Site_9/FM_Issue.html

              Edit note: converted link text to live hyperlink to make it easier to view the file---PhilModJunk

              • 4. Re: IWP:  relational database
                philmodjunk

                Hmmm, what version of FileMaker Server is Triple 8 using to host your file?

                "with an ID number keyed onto the ID field, the user selects "submit", and in a few seconds the updated record is returned for viewing."

                On which table is this layout based?

                In which table is this ID field that you've put on this layout defined?

                Do you have a script that runs when the submit button is clicked? If so what does it do?

                How does the dispatch log load the 4 values. Looked up value settings in field options? Auto-entered calculation? (Neither is really needed in actuality, you should be able to link directly to the fields in their related table instead of loading the info into a different table.)

                On a design note that applies to your database whether or not you web publish it, it looks like the Dispatch log table may not be correctly linked to the TS Hours table. As currently designed, you can have multiple records in the Dispatch log table with the same Volunteer ID number and every one of these records will link to the identical set of related TS Hours records.

                • 5. Re: IWP:  relational database
                  wiz302pa

                  1. The ID number is defined in VD.  It is used to load data into TS and DL by way of links.

                  2. I have no scripts running

                  3. The layout we are using when the problem arises is DL (this is where the ID number is entered and the 4 numbers are to be loaded)

                  4.  The values load directly with a link from VD (no lookup or calculation)

                  5.  Your Design Note:  should I link TS directly to VD instead of through DL?

                  6.  Contacted 888.net - they have me on a FM Advanced Server version 8. I have requested to be moved to a version 11 server.  Waiting for their response.

                  • 6. Re: IWP:  relational database
                    philmodjunk

                    1. What kind of "links"? It's possible to "load" several different ways--including just including the fields from the related table on your layout.

                    3. I take it Volunteeer Database::VolunteerID is an auto-entered serial number with DispatchLog::VolntrID defined as a data field of the same type (both text of both number). I think that contrary to what you said in your answer to question 1, you'll find that the field on this layout is defined in DispatchLog, not in VolunteerDatabase. Thus, you can create a new record on this layout by either entering a VolunteerID number or by selecting it from a value list. If you double click this field while in layout mode and find that it IS the field from Volunteer Database, I'm puzzled as to how this works when you are not using a browser to access the database. It might explain the blank fields, however, as entering/selecting a number in the Volunteer Database::VolunteerID field from the DL layout could, if allow creation of records via this relationship were enabled, create a new record in the Volunteer Database and thus the other fields would be blank. The problem here is that I'd expect the same behavior when accessing this via a FileMaker client instead of a web browser.

                    4. That appears to answer my question in 1. above. You are adding fields from the Volunteer Database table to your Dispatch Log table to show this data?

                    5. Depends on what you want this relationship to do. What do the records in TS represent? It may be that you need a second auto-entered serial number defined in DL to match a specific record in DL to one or more records in TS.

                    6. I thought that would be your answer. I know of another customer of triple 8 and they told me that was the version. I don't see anything here that would fail with version 8 yet, but I'm still trying to figure out how this works at all even without the limitations of web publishing.

                    • 7. Re: IWP:  relational database
                      wiz302pa

                      I have requested and received a new server with 888.net that is running FM Server 11 (not 8 as before).  I seems to be working correctly.  We will test it for a few days and let you know how it works.

                      At this point, it looks as if you question about the Server FM version led me to the answer.  We shall see.

                      • 8. Re: IWP:  relational database
                        wiz302pa

                        Followup question NOT associated with IWP:

                        I am writing a script that does the following:

                        1.  sort and find a set of records in table A

                        2.  Go To Record/Req/Page [first[] in table A

                        3.  create a new record in table B

                        4.  use Set Field script step to select information from table A to new record in table B

                        PROBLEM:  doesn't work

                        If I put text "xxxxxxx" in the calc part of Set FIeld, the text comes across to table B but none of the data does.  I have tried dozens of ways to get this done, but no luck.

                        • 9. Re: IWP:  relational database
                          philmodjunk

                          Is there a relationship between the two tables? If so what kind of a relationship? One to one? one to many?

                          If theres a one to one relationship between records in the same table, there's a simpler solution possible.

                          Here's a generalized solution that does not rely on such a relationship (or any relationship for that matter) between the two.

                          after find and sort, your script does this:

                          Go To record/request/page [first]
                          Loop
                             Set Variable [$Value ; Value: TableA::Field ]
                             #Do the above step with different variables for each value in TableA that you need for the set field step you'll use later to put a value in TableB.
                             Go to Layout [TableB]
                             New Record/Request
                             Set Field [TableB::Field ; $Value //or an expression that combines $Value with other values]
                             Go To Layout [Table A]
                             Go To Record/Request/Page [next ; exit after last]
                          End Loop

                          If there is a relationship linking the two tables, the only value you need to put in the variable is Table A's primary key. You'd use set field to set the foreign key field in Table B, Commit the record and then your set field expressions can refer to fields in Table A as you have now linked your new record to the current record in the TableA layout.

                          With a 1 to 1 relationship that has "allow creation of records via this relationship" specified, you can do this without ever leaving the layout for Table A. YOu can do set field steps to Table B from the Table A layout and the first such set field step will create the related table B record and copy the primary key value from Table A into the Foreign key field in Table B.

                          • 10. Re: IWP:  relational database
                            wiz302pa

                            Phil----

                            Your recommendation worked perfectly the first time I tried it.  Thanks for the quality, timely easy to understand help.