Familiar with SQL? A calculation field using ExecuteSQL and with the space character as both record and field separator could produce your field of combined data
ExecuteSQL ( "SELECT \"Time\", Notes FROM YourTableNameHere a WHERE a.\"Date\" = ?" ; " " ; " " ; YourTable::Date )
(Warning: ExecuteSQL has to have the syntax exactly right or you get nothing but a ? result. I haven't tested the above SQL so a typo on my part might keep it from working without further modification.)
Define a self join relationship that matches by the date field:
YourTable::Date = YourTable 2::Date
Define a calculation field, TimeNote as Time & " " & Notes
Then Substitute ( List ( YourTable 2::TimeNote ) ; ¶ ; " " )
would produce your single row combination of Time and Notes Data.
I did the self join relationship. Worked great!
But now...maybe it's been a long day. How can I get all of one's weeks output, all 7 days, on one layout?
If each row in your screen shot is a different record, then you need only perform a find to find records for a specified week--and that can automatically be the current week if you want.
See this thread for examples of scripted finds: Scripted Find Examples
Yes, I get that. But right now, what I get is this:
So you are getting the exact same data repeated over an over?
If so, something is not right with your layout. What does it look like in layout mode? Do you see :: preceding the field names?
I'm getting the same data repeated once for each entry. Yes, I see :: preceding the field names.
Which table should the layout be showing from, and which table should the field be from?
I've tried it in all combinations, same results.
I assume Substitute ( List ( YourTable 2::TimeNote ) ; ¶ ; " " ) is it's own Field, I called TimeNote2
Your posted example doesn't repeat it once, the 1-14-2014 entry repeats many times.
Does the 1-13 entry appear multiple times?
To help you figure this out, I need to know the current set of tables and relationships. Can you upload a screen shot of Manage | Database | Relationships?
Please also include a narrative description of your tables and relationships to help me understand what data is in each table as that is often not obvious to someone who didn't design the DB in the first place.
You saw only a snippet. For example, there are 17 entries for 1/22/2014. In the layout, the concatenated list shows 17 times: one for each separate entry. Which makes sense. There are still 17 entires!
There is only one table.
Also the time isn't being sorted properly.
0800 Meds given. 0900 Meds given. 1800 Grocery shopping. 1900 Assisted Reva in the bed. Checked, changed and applied ointment to her bottom. 2000 Meds were given. 2100 Reva was checked prepared for bed. Light out. 1000 Reva had breakfast she ate 100%. 1200 Meds given. 1300 Reva had lunch she ate 100% 1330 Reva was checked,changed,cleaned,cream applied to bottom. 1400 Meds given. 1600 Meds given. 1700 Meds given. 1730 Reva was checked,changed,cleaned,cream applied to bottom. plus dinner she ate 100%.
And the fact that there is only one table is part of the problem here. A table with one record for each day for each patient would eliminate the duplication.
You could also replace the body of the layout you posted earlier with a Sub Summary layout "when sorted by date" and that would eliminate the duplications.
But what you have seems a needlessly complex approach.
If you set up a table with one record for each patient for each day, You could set up a list view layout listing the date in column one and a portal in column two could list all the individual entries with no need for a special calculation to combine them. That would seem both easier to read and simpler to set up.
Each record in the portal could include a time field and you could sort your records by that time field to keep the entries in order even if they are not entered in correct order.
Note: in the original calculation, you can also get the entries into order if you sort your relationship by the time field.
It was not originally created by me, and there are 1000s of entires, but perhaps it needs rework.
I'll try and rework some of this tomorrow.