1 2 Previous Next 15 Replies Latest reply on Apr 28, 2011 9:06 AM by philmodjunk

    Creating report based on the text of a particular field

    MandeepLehal

      Title

      Creating report based on the text of a particular field

      Post

      I have a easy question, I am very inexperienced in the filemaker

      I have Tables – “Client Details” and “Progress Notes”

      I have various records for different clients in the "Progress Notes" table, I have fields “Client”, “Session #” etc.

      I want to generate a report for a particular client (may be pick name from drop down menu) where I can see the history of my previous sessions with him.

      What script do I need ?

        • 1. Re: Creating report based on the text of a particular field
          philmodjunk

          How did you link the two tables?

          You can use that link to find the Progress Notes for a given client record on a layout that's based on your Progress Notes table.

          You can also place a portal to the progress notes on you Client Details layout and use it to review and annotate your session notes.

          • 2. Re: Creating report based on the text of a particular field
            MandeepLehal

            Thanks for the reply.

            I think putting the portal will be better, for some reason I cannot make it work......sorry I am very new to it.

            what I want is that if I enterd a client name in "Client" field I want his previous session history shown in the portal below. Session number is important to me. 

            But all I see is what I have entered in the current session.

            • 3. Re: Creating report based on the text of a particular field
              philmodjunk

              I suggest looking up "portal" in the FileMaker help system to learn the basic details there.

              You'll need to create a relationship such as this in Manage | Database | Relationships:

              ClientDetails::ClientID = ProgressNotes::ClientID

              Where ClientID is defined as an auto-entered serial number in ClientDetails and is a simple number field in ProgressNotes.

              You could match by client name, but this creates problems when you get two clients with the same exact name, a client changes their name, or you enter a clients name incorrectly, log some notes and then discover and try to correct the error...

              If you enable "allow creation of records via this relationship" for ProgressNotes in the above relationship, you can place a portal to ProgressNotes on the ClientDetails layout and log new notes just by entering text into the bottom blank row of the portal. (And portal rows do not have to be the narrow one line of text per row format you see when you first create the portal. You can resize the row to handle a larger note field and the note field can have it's own scroll bar if you want.)

              • 4. Re: Creating report based on the text of a particular field
                MandeepLehal

                Thanks Phil, I was able to create the portal.

                I have two other issues/questions.

                1. What are the sorted/unsorted records ?. I created 5 clients and it is showing 2 of them unsorted and 3 sorted. When I run the report for Client list, it just shows me sorted or unsorted records in the list view of my report.
                2. I have “_kp_ClientID” my primary key in table “Client Details” which is a sequential “number” starts from C00001 and table “Progress Notes” has a field “_kf_ClientID”  (a simple “number” field) which is a “Drop Down List” and it pulls the values from list “ClientID_List”. “ClientID_List” gets values from table “Client Details” and field “_kp_CliendID” and also displays values from field “NameFull” which is in the same table.

                -          If I check the option to display values only from the second field, then  I only see one Client in the drop down list.

                -          If I go back and change the option to see values from only one field “_kp_ClientID”, then I see all the serial numbers (C00001, C00002 ……. C00005), but I need to see the client names as well.

                -          if I go back and select the option to display values from second field “NameFull” also, but this time don’t check the option to display values from only second field and sort by second field then I can see all the clients, but now they are not sorted with the names.

                What I am doing wrong here ?

                • 5. Re: Creating report based on the text of a particular field
                  philmodjunk

                  1.
                  Sorted/unsorted tells you the sort status of the current found set. If you have selected Sort from the reocrds menu to sort the records or a script sorts the records, you'll see "sorted". If the current found set of records are not sorted, they will be listed in the order that they were created and you'll see "unsorted". If you want to check exactly what order was specified for the current set of sorted records, you can select Sort from the records menu and it the dialog that opens will show you the order.

                  2.
                  I'd first check NameFull and make sure that it has the data in it you expect and that this is a field of type text and not of type number. Something isn't quite right here so you'll have to examine these details to find the problem. Showing only values from the second field will drop out duplicate names, by the way. If you want to hide the numbers and only show names, you'll need to use a validation rule on FullName that insures that they are unique or use some other calculation field that combines the name with additional data such as an address and/or phone number. (Otherwise, even if you could have duplicate names, you'd get two identical names in your list and you wouldn't be able to tell them apart anyway.)

                  • 6. Re: Creating report based on the text of a particular field
                    MandeepLehal

                    Thanks Fill.....actually my "NameFull" field is by the calculation "Evaluate ( "NameFirst & \" \" & NameLast" ; [NameFirst ; NameLast] )"

                    I tried it by a text field (NameFirst) as you said and it worked fine. So my problem was that I was not using a text field. Is there any other way I can define my "NameFull" field so that I can use it as a text field ?, If not what will be the other option to identify the particular client ?,

                    How can I do the validation or calculation so that I can see the duplicate names ?

                    Also on the "Client Details" layout I want to make the Navigation button and display like "Client 1 of 7", how can I do that ?

                    • 7. Re: Creating report based on the text of a particular field
                      philmodjunk

                      Calculation fields work just fine and you don't need the evaluate function here. Just make sure that the caclulation is a stored field that returns text, not number as its return type.

                      You can rewrite your expression like this and it should produce the same results:

                      NameFirst & " " & NameLast"

                      • 8. Re: Creating report based on the text of a particular field
                        MandeepLehal

                        Great !!!...works fine now… I am using  “  NameFirst & " " & NameLast & " " & DOB  “ to avoid the same name clients.

                        The last thing I have is that when I am in Progress Notes layout, I have fields _kf_ClientID, NameFull and DOB. Field _kf_ClientID is choosen by drop down list and related fields NameFull and DOB gets automatically filled with that. I have other fields (from table ProgressNotes) on this layout like “SessionNumber”, “DateofService”, “Time”, “Location” etc.

                        The issue is if I have a Session open for a client and I choose some different client from the _kf_ClientID drop down list, it automatically changes the “NameFull” and “DOB” fields and other information in the fields like “SessionNumber”,  “DateofService”, “Time”, “Location” etc. just stays there from the last client and it assigns all that session information to client that I pulled from the drop down list, how do I avoid this issue ?.

                        May be what should happen is that if I pull some other client from the list it should clear all the information from other filed or may be change all the  information in other fields for the last session of the client that I pulled from the drop down list….. not sure.

                        • 9. Re: Creating report based on the text of a particular field
                          philmodjunk

                          You might be better off using a portal to Progress Notes placed on your Client Details layout. That way, you can find the client record first and any note you record will be automatically linked to that client. You can enable "Allow creation of records via this relationship" for Progress Notes in the relatioinship between these two tables and then you can enter notes just by entering data in the bottom blank row of a Progress Notes portal.

                          In your case, you appear to be on the progress notes layout and are changing the existing record from a note for Client A to a Note for Client B. Since you only change the Client ID field, the fields from the related record change, but the local fields haven't changed because nothing has taken place to change them. You could add a OnObjectSave script trigger on the drop down list that runs a small script that uses:

                          Set Field [ProgressNotes::SessionNumber ; "" ]

                          for each such field to clear them every time you select a different client in the drop down.

                          • 10. Re: Creating report based on the text of a particular field
                            MandeepLehal

                            I am on Layout "Progress Notes" when I do this.

                            Field _kf_ClientID, NameFull and DOB are from table "Client Details"

                            Fields “SessionNumber”, “DateofService”, “Time”, “Location” etc. are from table "Progress Notes"

                            • 11. Re: Creating report based on the text of a particular field
                              philmodjunk

                              Field _kf_ClientID should be from Progress Nots, not Client Details.

                              (kf stands for key, foreign. __pk_clientId would be the matching field from Client Details and it should not be present on this layout.)

                              • 12. Re: Creating report based on the text of a particular field
                                MandeepLehal

                                Sorry for the confusion, _kf_ClientID is from "Progress Notes".

                                But how about the other fields, like DateOfService, Location etc ?......they still have the information and it assign that information to the client I pulled from the drop down list......may be I have to do the same thing for other fields ?

                                • 13. Re: Creating report based on the text of a particular field
                                  philmodjunk

                                  Please re-read the previous posts. I think you missed one. Wink

                                  I asked a question about the layout, then figured out the answer, deleted that post and put up a new one. In the mean time, you were fast enough out of the blocks that you read my first response before I could delete it and I think you missed the new one I put up at that time.

                                  • 14. Re: Creating report based on the text of a particular field
                                    MandeepLehal

                                    That right, I was too fast on that :)

                                    I did set up "Set Field [ProgressNotes::SessionNumber ; "" ]" for all other fields in this layout, but what happens now is that when I pull some other client from the list it deletes the session record of client that I previsouly had in over there. I dont see that session in the Portal that I have in "Client Details" anymore.

                                    I like you idea of entering the information in the portal in "Client Details" but I need some bigger fields at least 500 x 50 pixels, not sure how will that go in the portals.

                                    1 2 Previous Next