1 2 Previous Next 15 Replies Latest reply on May 29, 2014 9:07 AM by philmodjunk

    Related Table Field Not Showing on Form View

    edchua

      Summary

      Related Table Field Not Showing on Form View

      Product

      FileMaker Pro

      Version

      13

      Operating system version

      Windows 7

      Description of the issue

      I created two tables. First is called "Asset" with the following fields.
      AssetID (number, Auto)
      AssetName (text)
      rentPerHour (number).

      Second table is named "Rental" with the following fields.
      AssetID (number)
      StartDate (date)
      hoursUsed (number)
      AmountDue (calculation, defined as hoursUse*Asset::rentPerHour)

      The two tables are related via the AssetID.

      Steps to reproduce the problem

      Layout are as follows:

      AssetID   (edit box)
      StartDate (edit box)
      hoursUsed (edit box)
      AmountDue  (edit box)

      Expected result

      I am expecting to see something like this with the AmountDue calculated.

      AssetID : 001
      StartDate : 03-15-2014
      hoursUsed : 60
      AmountDue : 3,500

      Actual result

      But instead, FileMaker has completely ignored the calculation field. The AmountDue remained to be zero. Seems like FileMaker has a bug. I had reviewed the table design, relationship more than ten times but to no avail.


      AssetID : 001
      StartDate : 03-15-2014
      hoursUsed : 60
      AmountDue : 0

      Exact text of any error message(s) that appear

      No error message shown or reported.

        • 1. Re: Related Table Field Not Showing on Form View
          philmodjunk

               Layout are as follows:

               And is the layout based on Asset or Rental?

               AmountDue (calculation, defined as hoursUsed * Asset::rentPerHour)

               And did you define a number field with an auto-entered calculation field or did you define a calculation field with number as the result type?

               If you defined it as an auto-entered calculation, did you clear the "do not replace existing value..." check box?

          • 2. Re: Related Table Field Not Showing on Form View
            edchua

                 Hi PhilModJunk,

                 Thanks for your reply. To answer your first question. The layout is based on "RENTAL" in form view. The field "AmountDue" (belonging to table Rental) is defined as "Calculation" type with number as result type. Yes, I did clear the check box [ ] Do not evaluate if all referenced fields are empty. Also, I tried to clear the check box [ ] Do not store calculation results -- recalculate when needed, but unfortunately, that action has been stopped by the message "The calculation "Amount" cannot be stored or indexed because it references a related field, a summary field,an unstored calculation field, or a field with global storage. I couldn't figure out why it's not working.

                 It could have been a piece of cake if it was in MS ACCESS. I could do almost anything

                 Ed

                  

            • 3. Re: Related Table Field Not Showing on Form View
              TSGal

                   ed chua:

                   Thank you for your post.

                   My initial thought is that you have more than one entry in the Asset table with AssetID = 1.  In that case, the first entry will be used, and if the rentPerHour field is either blank or 0 (zero), you will get a zero result.

                   Just to be sure, I followed your example.  Although I didn't have the contents for your rentPerHour field, I substituted 60, and I did get the result of 3,600 in the AmountDue field.

                   TSGal
                   FileMaker, Inc.

              • 4. Re: Related Table Field Not Showing on Form View
                edchua

                     Hi Guys,

                     I don't think my problem has been answered nor solved yet. Anyway, I am posting some images to show you how I arrived at the results in question. Hope to hear some workable solution. Thank you.

                      

                • 5. Re: Related Table Field Not Showing on Form View
                  TSGal

                       ed chua:

                       In the Asset table, I do see the AssetID = 1.  However, I don't see the AssetID field displayed in the Rental table.  Can you verify the AssetID for the displayed Rental record is 1?  Also, make sure both fields are of type Number.  Otherwise, if there is no related record, the value will be 0 (zero) since no related record can be found.

                       Perform a search on both table for AssetID = 1.  There should only be one record found in each table (unless you have one-to-many relationship set up).

                       TSGal
                       FileMaker, Inc.

                  • 6. Re: Related Table Field Not Showing on Form View
                    edchua

                         Hi TSGal,

                         To answer your first question, YES. AssetID is the primary key of table ASSET while AssetID is the foreign key of table RENTAL(it's part of a compound key CusID+AssetID). Yes, both AssetID are of type NUMBER. You don't see the AssetID displayed on the RENTAL LAYOUT because it's hidden in the drop down box (with 2 columns, AssetID & item description). Answer to second question, Yes the relationship between Asset and Rental is one-to-many.

                         My concern is : Does the calculation field of table RENTAL work if one of the fields in the calculation(hours_used x rate_per_hour) is a field from a related table? The calculation field works if I INTENTIONALLY include the Rate_per_hour field in the RENTAL table. In this case, both hours_used and rate_per_hour will be local fields of table RENTAL, instead of the other (Rate_per_hour) being a field of the related table ASSET.

                         I guess my explanation is clear. PLEASE FIX THE BUG!!!

                    • 7. Re: Related Table Field Not Showing on Form View
                      TSGal

                           ed chua:

                           This should work with the relationships set up properly.  I have a sample file using the data from your screen shots that I have sent to our Technical Support contact.  You should receive the file shortly.  If you don't receive the file within one hour, please let me know, and I'll make sure the file is sent to you immediately.

                           TSGal
                           FileMaker, Inc.

                      • 8. Re: Related Table Field Not Showing on Form View
                        edchua

                             Hi TSGal,

                             I had received your file. Yes, It works if AssetID is an Edit Box. But if you change it into a Drop Down. Then AmountDue wouldn't compute anymore and displays a 0(zero) value. I sent the file back through STEVE. Do you have another solution?

                              

                        • 9. Re: Related Table Field Not Showing on Form View
                          TSGal

                               ed chua:

                               I received your file.  Thank you.

                               The AssetID field in the Rental table contains alphabetic characters and you defined it as a Number.  This will never match the AssetID Number value in the Asset table regardless if it is an Edit box, pop-up menu, drop-down, etc.  If you instead contain the pop-up value "1" (or enter the value 1) into the AssetID field, then it will match the AssetID field in the the Asset table, and the Amount payable field will then show a value.

                               TSGal
                               FileMaker, INc.

                          • 10. Re: Related Table Field Not Showing on Form View
                            edchua

                                 Hi TSGal,

                                 Thanks for your quick response. I don't know the mechanism on how FileMaker access data in related table. But that's how it is being done in Microsoft ACCESS Forms. One doesn't memorize AssetID code, especially if there are hundreds of item on the list. We simply click on the item description involve when preparing a sales invoice. The AssetID is hidden in the drop down box(1st column. 2nd column contains the item description). Unfortunately, this strategy doesn't seem to work in FILEMAKER despite display data from Rental::AssetID, which is suppose to match the AssetID in the related table ASSET(to retrieve Rate_per_hour).

                                  

                                  

                            • 11. Re: Related Table Field Not Showing on Form View
                              philmodjunk

                                   Essentially the same method can be used in FileMaker. You set up a value list where the Asset ID is specified as the source of values for the "field 1" value in the listand you can specify a name or description field for "field 2". The ID's in Field one can be hidden if you select that option.

                                   And that describes the simple "beginner level" option for setting up a such a value list. Other more sophisticated and more user friendly options are also possible.

                                   But what TSGal is pointing out is that the ID field in one table should have the same data type as the Id field used in the other table in the other half of the relationship. Text should be put in text fields and numbers into fields of type numbers--with very few exceptions.

                              • 12. Re: Related Table Field Not Showing on Form View
                                TSGal

                                     ed chua:

                                     For your original drop-down field,, you are using the Value list "AssetName".  You have only specified the Asset::Item field.  Instead, edit the field to select the AssetID field, but also display the value from the Item field.  Below that, select the option "Show values only from second field".  This way, you can select the Item, but the AssetID field will contain the correct value.  See the screen shot below:

                                     TSGal
                                     FileMaker, Inc.

                                • 13. Re: Related Table Field Not Showing on Form View
                                  philmodjunk

                                       The dialog box used in FileMaker is vaguely similar in process to what you use for an MS Access Combo box but the FileMaker options are far more primitive and limited than what you can do in MS Access. In Access you can set up an SQL query that specifies multiple columns of data. FileMaker's multiple columns is exactly two such columns and you can't define a calculation to construct a new column from existing fields in the source table.

                                       But in both systems, you can choose to hide the ID column (field) while displaying data from a Name or description column.

                                  • 14. Re: Related Table Field Not Showing on Form View
                                    edchua

                                         Hi PhilModJunk,

                                         Thanks for that lengthy explanation. It's clear to me now that FileMaker has certain limitation. Hope the next version will solve that problem.

                                    1 2 Previous Next