1 2 Previous Next 19 Replies Latest reply on Mar 29, 2012 6:46 PM by BruceHerbach

    Noob - How to create a from from query/report

    MichaelMeyer

      Title

      Noob - How to create a from from query/report

      Post

      Hi Guys

      I am a noob to FMP, but hoping some people might be able to give me a hand with some pointers. I have created a multi table DB easily enough. Three Tables

      Schedule - ID, Date, Resource, Customer

      Customer - ID, Customer Name

      Resource - ID, Resource Name

      Relationships all setup and seem to be OK - One  to Many

      I want to create a view/form where I cen enter a date and then present a weeks worth of data in a table

      Row = Date

      Column = Resource, Customer

      If a field is blank for the designated week or no data, the form would facilitate entering data and view allocations.

       

      I have no idea really where to look, I am an old MS Access person who probably would have used a query to generate a form, but since learning some basics of FMP, I have no idea which direction I need to head.

      I hope I have been able to convey enough information, any assistance is appreciated.

       

      Cheers

      Mike

        • 1. Re: Noob - How to create a from from query/report
          MichaelMeyer

          Perhaps a better way to think of the view that is in my mind

           

                           Date          Date          Date          Date          Date          Date          Date

          Resource    Cust          Cust          Cust          Cust          Cust          Cust          Cust

          Resource    Cust          Cust          Cust          Cust          Cust          Cust          Cust

          Resource    Cust          Cust          Cust          Cust          Cust          Cust          Cust

          Resource    Cust          Cust          Cust          Cust          Cust          Cust          Cust

           

          I should probably add, I only want to see resources based on which year is against them, there is an additional field for the year of a resource

          • 2. Re: Noob - How to create a from from query/report
            philmodjunk

            That second post helps a lot. Before I scrolled down to see it, I was picturing a different layout for your data. What you describe is called a "crosstab" query and Access has  wizard for setting up such queries while FileMaker does not. Nevertheless, such an arrangement for your data is possible in FileMaker.

            Be fore we go there though, be advised that arranging the data in the following format is much more flexible and takes much less effort to create:

            Resource 1
                   Date     Cust
                   Date     Cust
                   Date     Cust

            Resource 2
                   Date      Cust
                   Date      Cust

            Let me know if you think such a "summary report" would work for you.

            One thing you didn't spell out was the exact relationships you have in place linking your three tables. I'm guessing that you have this:

            Customer----<Schedule>-----Resource

            Customer::ID = Schedule::Customer
            Resource::ID = Schedule::Resource

            If so, you can base a List View layout on Resource and use one row filtered portals to arrange data from Schedule and Customer in columns:

            In layout mode add the Resource Name field to the body to make the first column. For the second column add a portal to Schedule. Give it a portal filter expression that filters out all records except those of a specified date such as:

            Schedule::Date = Resource::GlobalDateField

            Add the customer name field from customer to this one row portal to display the customer name.

            To add the remaining columns, you draw a selection box around the portal and then copy and paste additional copies of it to add the rest of the columns. Open portal setup for each one and update the filter expression to match to a different date. You can use expressions such as Resource::GlobalDateField + 1, Resource::GlobalDateField + 2, etc to get columns where the specified dates are all in a series based on a single specified date.

            Be sure to specify a date field with global storage for the filters as this allows a single value to apply to all resource records on your layout.

            Note: When you select different values in the global date field, the layout, as currently designed will not automatically update to display data based on the new date, until a script performs Refresh Window [flush cached join results]. By using a more sophisticated relationship between Resource and Schedule that includes the global date field and uses the X operator instead of = can eliminate the need for a script using this step--which can really slow down layout updates especially with large numbers of records and/or slow networks.

            • 3. Re: Noob - How to create a from from query/report
              MichaelMeyer

              Thanks for the reply, the view does matter and needs to appear as in the secod post.

              Relationships:

              Resource Table          Schedule Table           Customer Table

              ID                             ID                             ID

              Res Name ----|          Date                     |--Cust Name

              Year              |--------Resource              |

                                              Customer-----------|

               

              Each is one to many with enforced integrity

              Doe the above change your suggestions?

              • 4. Re: Noob - How to create a from from query/report
                philmodjunk

                I'm afraid that I'm not sure that I can decode your relationships from your post. What fields match to which?

                Is this what you have?

                Resource Table----<Schedule Table-----<Customer Table    (---- means "one to many" )

                If so, how would you show just one customer on a given line for a given Resource on a given date?

                PS. Matching records in relationships using name fields instead of ID numbers is not a good idea. Names are not unique, they change and are vulnerable to data entry errors--all of which create problems for you that can be avoided if you use serial numbers instead.

                • 5. Re: Noob - How to create a from from query/report
                  MichaelMeyer

                  Oh well, I was trying a little ascii art hopeing it might help, but it did not work well

                   

                  Your right I did not take the relationships higher

                   

                  Resource ID ---------< Sched Resource

                                                  Sched Client >----------- Client ID

                   

                  We expect to only have one resource and client, but many schedules

                  • 6. Re: Noob - How to create a from from query/report
                    philmodjunk

                    Does that show one occurrence of the schedule table or two?

                    What you have is the relatioship I specified at the beginning as far as I can tell--provided you have an occurrence of Schedule that links to both Resource and Client. Thus, the solution I describe should work for you.

                    • 7. Re: Noob - How to create a from from query/report
                      MichaelMeyer

                      There are two occurances, they are two seperate fields for two different purposes. (Albeit they are in the same table)

                      I have no idea how to have a single field represent accross 3 tables

                      With the guidance you have provided in post 3, would this allow the results to appear as required in post 2? They layout I need to achieve is critical to this DB.

                      • 8. Re: Noob - How to create a from from query/report
                        philmodjunk

                        Two separate fields are fine. two different occurrences are not. YOu can make a new occurrence of clients and link it by Client ID to Sched Resource. This will not affect your existing relationships.

                        My assumption behinde every post save the first is that you want to see your data in columns by date scheduled. My suggested solution does this.

                        It occurs to me just before I post this message that you may be missing a field in Schedule. You need two ID fields in Schedule, one to link it to resources and one to link it to clients.

                        • 9. Re: Noob - How to create a from from query/report
                          MichaelMeyer

                          Perhaps I should have been more specific in the original post, I was jsut trying to keep it simple.

                          Customer Table

                          CustomerID    -----< SchedCustomerID

                          CustomerName

                          Schedule Table

                          Schedule ID

                          Date

                          SchedCustomerID >----- CustomerID

                          SchedResourceID >----- ResourceID

                          Resource Table

                          ResourceID -----< SchedResourceID

                          ResourceName

                           

                          Text is a pain at the best of times to convey these kinds of things, there are probably some good standards for DB doco, that I am simply not aware of

                          • 10. Re: Noob - How to create a from from query/report
                            philmodjunk

                            What you describe is exactly what you need. You just need to modify the occurrence of schedule that links to the resource table so that it also links to an occurrence of the Clients table.

                            You can upload a screen shot of your relationships tab quite easily by using the controls below the Post A answer box. Just make sure that your captured screen shot is of the specified file type.

                            • 11. Re: Noob - How to create a from from query/report
                              MichaelMeyer

                              OK I am not really following you, how does one do this "You just need to modify the occurrence of schedule that links to the resource table so that it also links to an occurrence of the Clients table."

                               

                              Thanks so much for you patience and assistance

                              • 12. Re: Noob - How to create a from from query/report
                                philmodjunk

                                This is what I am describing. The table occurrence shown in green is a new occurrence of client.

                                • 13. Re: Noob - How to create a from from query/report
                                  MichaelMeyer

                                  I am having problems creating a portal, and need to make a few changes too, hopefully not too impacting,

                                  View needs to look like

                                                   Date          Date          Date          Date          Date          Date          Date

                                  Resource    Cust          Cust          Cust          Cust          Cust          Cust          Cust

                                                   WorkType  WorkType WorkType  WorkType  WorkType  WorkType  WorkType 

                                  Resource    Cust          Cust          Cust          Cust          Cust          Cust          Cust

                                                   WorkType  WorkType WorkType  WorkType  WorkType  WorkType  WorkType 

                                  Resource    Cust          Cust          Cust          Cust          Cust          Cust          Cust

                                                   WorkType  WorkType WorkType  WorkType  WorkType  WorkType  WorkType 

                                   

                                  The Date Row needs to begin from a date specified (i.e its needs to ask for a start date) and the subsequent date fields are the seven days following there after. Even if there is no data in th schedule date, it needs to display it, and if a customer and work type is specified the records created

                                  The resource column needs to be generated by the resource table, there is a field called year, it is text (If can be changed) the whole year is recorded here (2011 or 2012 or 2011 2012) based upon the date entered above it needs to display the resources available to that one year, not all records

                                  I think this complicates it s bit further, any comments or suggestions?

                                  Attached is copy of Relationships

                                  • 14. Re: Noob - How to create a from from query/report
                                    philmodjunk

                                    Not by much. Your date row can be a global field followed by a series of calculation fields like this:

                                    gDate1 : Global field of type date
                                    cDate2 : calculation field with date return type; gDate1 + 1
                                    cDate2 : gDate1 + 2

                                    and so forth to get all the dates you want. you specify a date in gDate1 and the calculation fields will produce the rest of the dates.

                                    Your layout will be a list view layout that refers to Resources Copy in layout setup | show records from and you put the resource name field from resources copy directly on your layout to create the column of resource names. Then place filtered portals to schedule on your layout. Make them one row portals, but size the portal tall enough that you can place your customer field from customer above the WorkType field from Worktypes.

                                    The portal fiters, I have already described.

                                    To limit the report to a specific year simply requires that you perform a find for all resources dated with that year.

                                    1 2 Previous Next