Create another table occurrence in your graph of this table (known as a self join). Join the number field to itself using =
Then in your table, create a calculation (result is text) with:
Substitute ( List ( selfJoin::Name ) ; ¶ ; ", " )
This forum doesn't accomodate attachments and I'm currently not where I can post a sample (I'm on ipad only). Here are very easy instructions:
1. Select File > Manage > Database and select the Relationships tab
2. Highlight your table then click the ++ (lower left) to duplicate it.
3. Select the number field in the first occurrence and drag it to the second occurrence number. It will assume you want = which you do.
4. Name this second occurrence something like selfJoin
... then create your calculation. If you get stuck let us know and I can post an example in a few hours when I'm back home.
I fixed the issue of the 1s and the 1Bs being listed together by changing the number field to text. So, that seems to be working right now.
It seems the last thing that needs to happen now is to have 4 people listed as 1s, but then displaying them as two separate teams, instead of all 4 together. And, instead of displaying 4 records, there should only be 2 after I am finished.
.....needs to end up being:
Fred, John 1
James, Harold 1
not, Fred, John, James, Harold 1
I believe that you are attempting to use a field value when a relationship is what is needed. Consider:
If you have two people assigned to Team=1A and two people assigned to Team=1B, then a calculation of GetAsNumber (Team ) will combine them into the fourthsome.
If you had a table called Teams with auto-enter serial with 1, 2 , 3, 4 ...
Then your Players table where they are assigned to the Team with 1, 2, 3, or 4. You can restrict to two players. If relationally structured, this would be simple because you would just display your Players through the relationship on the Teams layout as I've suggested, using List() through the relationship.
From your data example
How do you know which records to put on the same team?
And why use letters/numbers like this. If you are going to have to enter data to identify how the records should be grouped, why not use something more descriptive like some actual names?
And while a self join is definitely one option for this, another is to set up a table of groups and/or teams (you seem to have both here) with a primary key that you use to link individuals to that group or team table.
Also, please note that with each post, you are revealing additional details not previously stated. I suggest a more complete description of what you are trying to do so that those helping you do not waste time suggesting things that may fail simply because they don't have a complete picture of what you are trying to accomplish.
The list of names is sorted prior to creating their groups, so when they are listed as shown above, the next step is to manually assign them their number. The numbers identify more than just their groups and they must remain as 1, 1A, 1B, 2, 2A, 2B, etc. The problem complicates ( at least in my mind ) because sometimes these groups are "groups" and sometimes they remain as individuals, but they are assigned to sit at the same table ( 1, 1A, 2, etc ).
I'm trying to create signs that indicate which table the individuals or groups are supposed to be sitting at. There could be 4 people sitting at the same table with two people on one side and two people on the other. For other tables, there could be only two people sitting at a table. There are only 2 chairs on each side of the table, that's why for some table numbers ( 1, 1A, 2, 2A, etc. ) there is only one group.
Sorted by what criteria?
And why does this require such an abstract numbering system? (Don't want to argue the point, but if we know why, it can help us refine our suggestions.) Why both numbers and letters?
and this still does not explain how/why
Would be divided into two teams of two individuals nor how you would pick which two make up a team.
The sort criteria is irrelevant. The user looks at the records in front of them and adds Table numbers ( not tables as in FMPRO, but Tables as in sitting around eating on or playing cards, etc. ) The tables are labeled as Table 1, Table 1B, Table 2, Table 2B, Table 3, Table 3B, etc. This is a traditional practice that cannot be changed to something else.
Each person in the list is assigned to a sitting Table. There are two chairs on each side of the Table.
Fred sits next to John on one side of Table 1.
James sits next to Harold on the other side of Table 1.
They are all sitting at Table 1 and they need a sign that tells them which pair of them sit on which side of the Table.
The signs simply say
Fred, John 1
James, Harold 1
As it is now, since Fred, John, James and Harold all have the number 1 assigned to them, the sign that is created says:
Fred, John, James, Harold 1, and there are four signs that would print. I need only one sign to print per group of two.
Sorry, this is something that is likely very obvious to you, but it's not at all obvious to me.
Why would this produce:
Fred, John 1
James, Harold 1
Fred, Harold 1
James, John 1
Fred, James 1
Harold, John 1
This is a traditional practice that cannot be changed to something else.
I'm not suggesting that you should change this, but understanding the "traditional practice" might affect what implementation is most user friendly and most error resistant. What do the letters mean? Are all tables the same?