# Find which 'values' are still missing.

## Recommended Posts

I did some googling, but could not find an answer, so I hope some of you can help me out.

Here's the thing:

I have 2 tables with a relation.
Table 1, 'BOX' has the field 'BoxColor' with 3 records: Red, Blue and Green.
Table 2, 'CONTENT' has the relational field 'BoxColor' and the field 'NumberInBox'. there are several records with different numbers (let's say 1-9) in the different colored Boxes.

Now in the end, I have 3 Boxes with Numbers in it for example:

Red: 1, 3, 3, 5 and 8
Blue: 3, 2, 8 and 4
Green: 5, 4, 7, 8, 2, 2 and 1

Now my Question:
How can I list/find the missing numbers (in the list: 1-9)
So the answer should be like:

Red Missing: 2, 4, 6, 7, 9
Blue Missing: 1, 5, 6, 7, 9
Green Missing: 3, 6, 9

What would be even more nice, count how many each number occurs, and include the 0's
So:
___Red_Blue_Green
1|....1......0......1
2|....0......1......2
3|....2......1......0
4|....0......1......1
5|....1......0......1
6|....0......0......0
7|....0......0......1
8|....1......1......1
9|....0......0......0

I hope I made myself clear as my English may be bad ;-)

Numbers in Box.fmp12

##### Share on other sites

I think this custom function may be able to assist you in determining the values that are missing

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Who Viewed the Topic

4 members have viewed this topic:
AlesD  xochi  sal88  rivet
×
×
• Create New...