9 Replies Latest reply on Apr 14, 2014 8:49 AM by philmodjunk

    Relationship Question



      Relationship Question


           Table A is related to Table B with a unique serial #

           Table B has a field "DateCancelled" that is populated with a date if the account is cancelled.

           Layout based on table A.

           I would like to omit records from the report based on Table A if the date in Table B contains a date.

           How do I do that?


        • 1. Re: Relationship Question

               Enter find mode
               Specify a * in the date field from table B
               Select the Omit records Option
               Perform the find

               The above process can be manual or scripted.

          • 2. Re: Relationship Question

                 Entering * to find any values results in nothing being found but this field definitely contains values in some records in Table B. 

                 There must be something wrong with the relationship between these two tables. Table A is linked to Table B on field OrgID. This field is unique in Table B but not in Table A.

                 The DateCancelled field is in Table B but it is on a sub summary layout based on Table A. While in layout mode,  data from DateCancelled appears in this field. When I switch to Browse, the values disappear.

                 Most of the data on the layout are calculations and summaries from data in Table A.

                 I also tried to populate a field -- IsCancelled -- in Table A based on the the values of DateCancelled in Table B. This wouldn't work either.  But, a similar calculation in Table A -- Price Tier -- does seem to work.

                 PriceTier is a calculation in Table A that enters a number from 1 - 6 based on a value in Table B. It's a mystery!



            • 3. Re: Relationship Question

                   Did you specify the "omit" option when performing this find? * specifies that the field cannot be empty. By making it an "omit" request, we reverse this and find all records where the field is either empty or there is no related record.

                   Being able to see data in this field while in layout mode does not confirm that there are correctly linked records in the related table. That is a check to perform in Browse mode.

              • 4. Re: Relationship Question

                     Yes. I specified Omit The result was a found set of all records.


                • 5. Re: Relationship Question

                       I think there must be a problem with the relationship because I can't create a calculation field in Table A based on the values in the DateCancelled field in Table B.

                       29 records in Table B have a date value in DateCancelled. Searching DateCancelled from within Table B finds all 29 but finds nothing when searching if the DateCancelled field is on a layout based on the Table A.

                       Table A is the many side of the relationship. I want to omit records in a sub-sumamry layout showing records from Table A if the DateCancelled field in Table B has a value. To do this, I created a calculation field in Table A to enter a 1 or "x" if the OrgID's in Tables A and B matched. This is not working for some reason.

                  • 6. Re: Relationship Question

                         Some additional info that may help.

                         To test the relationship, I simplified the calculation for c_cancelled in Table A to test for any value in Date_cancelled in Table B.


                         I assumed that this would enter a 0 in c_Cancelled if Date_Cancelled wasn't empty.

                         Then, I created a layout in Table B with the ID that links the two tables and Date_Cancelled from Table B and c_Cancelled from Table A. 

                         c_Cancelled has a "1" in every record even if Date_Cancelled has a date showing.


                    • 7. Re: Relationship Question

                           Please describe the match fields of your relationship. What fields in Table A match to what fields in Table B. If any of these match fields are of type calculation, are any unstored calculations or do any have indexing turned off?

                           When on a layout based on Table B, can you place records from Table A on this layout and if so, do any show data from Table A?

                      • 8. Re: Relationship Question

                             The match fields are both numbers (not calculations) and indexed. Can't modify.

                             Yes. I can add a field from Table A to a layout based on Table B and the values do appear.


                        • 9. Re: Relationship Question

                               Then I do not see how you are getting the results that you describe here.