Is there a way to lock rows together?

Karin,

Oh my! That's a tough problem. There is a basic problem with the design of that database. Each record must be self-sufficient. Each record for an accession should be complete with Title and Location. You should not have to maintain proximity to another row for the record to be complete. You should spend your efforts right now to repair the design. Would you like some help with that?

Jerry

If I understood well, to achieve what you wish, you must fill the date field in every row.

This done you will have to create a new column in which the formula would be :
=""&year(A)&right("0"&month(A),2)&right("0"&day(a),2)&F

(I typed F as the column storing accessions from memory).

You may also create an other column with the formula :
=F&year(A)&right("0"&month(A),2)&right("0"&day(a),2)

Sorting upon the first added column, you will gather every transaction done the same day.
Sorting upon the second added column, you will gather identical accessions.

Yvan KOENIG (VALLAURIS, France) mardi 8 mars 2011 21:56:36

As separate records, your column titled "# of reels" will no longer be applicable. Instead, that column would be titled "Reel" and each reel will have a number. For example, the reels in 5/24/77A will have the numbers 1 through 9.

As separate records, the rows that are in blue will not be applicable and should be deleted. All rows will have the title and location. You can create your blue rows by using the Category feature, categorizing by Title, and using COUNT (also part of the categorize feature) for summing up how many reels are contained in each title. Also select "show function name" and it will display as "Count: #".

To find duplicate titles, you could create a column that concatenates the title and the reel number and use it to find duplicates. =A&C. There may be a 5/24/771 and 5/24/772, etc. but there should be only one of each unless you have a duplicate title. Ensure there are no duplicates before categorizing or they will get categorized together (which means they will also be sorted together).

I'm not sure what kinds of sorts you are planning to do. Some may require turning off the categories, some may not. Note that turning on categories will sort the table and it remains sorted even after turning categories off. If you keep the Reorganization panel open you can categorize, sort and filter the table in different ways without much difficulty. Option-clicking on a disclosure triangle in a categorized table will collapse the categories; in this case it would collapse it so that you have one row per title and a count of the number of reels in the title. Option-click again to reveal all rows.

Also, I recommend making a copy of your document before doing all this. Just in case.

Message was edited by: Badunit

karin_ane wrote:
It is impossible to detect duplicates as I go if each row is complete.

That's just not true. It may have some slight complexity, but it can be done.

I tried adding a separate column that would contain date information and be used only for sorting, but there can be multiple cans with the same date. I just need a way to link the different accessions to that one title and if I could just lock rows together it would be fine...

When sorting, you can do multiple-level sorts. Even better, you can create columns with aggregate-data tags to sort on. Badunit and Yvan have given you directions for that.

I must add that we aren't ignoring your request. It's just that I don't think any of us here, Yvan, Badunit and I at least, could in good conscience lead you down a path to disaster and data loss.

Jerry

Message was edited by: Jerrold Green1

Message was edited by: Jerrold Green1

I forgot to explain that I splitted dates so that we store them as yyyyMMdd which is the best format to sort date_strings.

Yvan KOENIG (VALLAURIS, France) mardi 8 mars 2011 22:19:00

There is no way to lock rows together other than having merged cells (you could merge cells in columns A-D, for instance). But then you cannot sort so, in effect, all rows become locked together.

Look at this screenshot



Rows remain locked together.

I split column A into two columns so that cells of column A contain dates.

In cell J2, the formula is :
=IF(LEN($A)=0,"X",""&YEAR($A)&RIGHT("0"&MONTH($A),2)&RIGHT("0"&DAY($A),2)&$B&"_" &$F)

In cell K2, the formula is :
=IF(LEN($A)=0,"X",IF(LEN(F)=0,OFFSET($F$1,ROW(),0)&" ",F)&""&YEAR($A)&RIGHT("0"&MONTH($A),2)&RIGHT("0"&DAY($A),2)&$B)

In cell L2, the formula is :
=IF($J2="X","",IF(COUNTIF($J,$J2)>1,"duplicate",""))

Then, apply Fill Down.

As you may see, when a row doesn't contain a record, the key is not set to "" but to "X" so that it remain at the bottom when we sort.

Of course, the formula flagging duplicate doesn't flag these 'empty' rows.

Yvan KOENIG (VALLAURIS, France) mercredi 9 mars 2011 12:03:21