12 Replies Latest reply on Feb 12, 2014 1:24 PM by LambdaEnt

    Multiple records, concatenated

    LambdaEnt

      Title

      Multiple records, concatenated

      Post

           I have a single table, non-realtional file. Records are entered with a date, time, and notes.

           I need a field that has all of the note entries for a single day, concatenated with the time.

           Attached is a quick and dirty example:

      Untitled.jpg

        • 1. Re: Multiple records, concatenated
          philmodjunk

               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.)

               or:

               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.

          • 2. Re: Multiple records, concatenated
            LambdaEnt

                 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?

                 Example below:

             

            • 3. Re: Multiple records, concatenated
              philmodjunk

                   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

              • 4. Re: Multiple records, concatenated
                LambdaEnt

                     Yes, I get that. But right now, what I get is this:

                      

                 

                • 5. Re: Multiple records, concatenated
                  philmodjunk

                       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?

                  • 6. Re: Multiple records, concatenated
                    LambdaEnt

                         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

                     

                    • 7. Re: Multiple records, concatenated
                      philmodjunk

                           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.

                      • 8. Re: Multiple records, concatenated
                        LambdaEnt

                             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!

                         

                        • 9. Re: Multiple records, concatenated
                          LambdaEnt

                               There is only one table.

                           

                          • 10. Re: Multiple records, concatenated
                            LambdaEnt

                                 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%.

                             

                            • 11. Re: Multiple records, concatenated
                              philmodjunk

                                   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.

                              • 12. Re: Multiple records, concatenated
                                LambdaEnt

                                     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.