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.
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!
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.
Yes. I specified Omit The result was a found set of all records.
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.
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.
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?
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.
Then I do not see how you are getting the results that you describe here.