2 Replies Latest reply on Jun 17, 2013 12:28 PM by haucke

    Work Order Report

    haucke

      Newbie again...

      Situation... SQL Server with Dynamics SL providing Data to a FileMaker Server which then provides data to iPad FileMaker Go databases. I get a Work Order Header records as well as reference data from SQL Server to FM Server via Table references within the FM Server. We have fully developed the interface for Labor, Material and Work description information gathering on the iPad and they sync back and forth with the FM Server just fine. The next hurdle is to develop a report for a completed work order print to email to customer. The trouble here is there may be a single labor line and/or single material line or multiple of each and the layouts do not appear to lend themselves to dynamically resizing. Yes if you have one group of records like labor or material it works fine but have multiple detail types (related tables) this does not appear to be possible. I thought about building a report table and stacking all the records on top of each other and reporting on that but the field lables will need to change when listing labor vs Material etc... and there is not a dynamic way of changing lables.... I could use text fields as labels but after a while things start getting ugly. I would like to back up and Punt (so to speak) and pull the data back into SQL Server where I could easily author an SSRS report; however I am missing one critical piece with regards to the data. We will be capturing signatures and I need to render the signature on the report. There does not appear to be a way to get that signature into SQL Server blob from an FM blob, so I am stuck there.

      I have watched a good lengthy video on reporting from the 2012 DevCon on Advanced Reporting Techniques and it has given me some ideas but before I go down that road I just wanted to make sure I was not missing a simpler solution due to my inexperience.

      Again I could generate an SSRS report in less than an hour to cover this if I could get the signature data out of FM and into SQL…

        • 1. Re: Work Order Report
          Mike_Mitchell

          There are three "standard" ways of combining data from different tables onto a single report in FileMaker.

           

          1) You've already mentioned one, the creation of a "scratch" table where the records are assembled and the report generated from there. The issue of headers can be addressed using text fields and summaries, although you've indicated things are "ugly" using this method. (BTW - Have you looked at merge variables as a way of dynamically allocating field labels?) It can work quite well, as long as you're careful not to let one user overwrite or otherwise interfere with another user's report records.

           

          2) The second method is somewhat similar, although quite a bit faster and more sophisticated. It's called the Virtual List, where you assemble one or more return delimited lists of data, then use calculated fields (generally using the GetValue function) to echo the values out to a list view report. Because you're assembling the report in memory, it's flexible and very fast, but does require some good knowledge of how FileMaker works with return delimited lists. (As an aside, you can use the ExecuteSQL function to assemble your lists, assuming you're using FileMaker 12.) Here's a few links on the technique:

           

          https://fmdev.filemaker.com/message/79840#79840

          http://www.mightydata.com/blog/taming-the-virtual-list-part-i/

          http://seedcodenext.wordpress.com/2011/11/05/virtual-list/

           

          3) The third method would be to create the report using HTML code. This gives you a great deal of flexibility, as you can build the report pretty much any way you want. Printing, however, can sometimes be an issue, given the vaguaries of web browers and how they print.

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Work Order Report
            haucke

            This is the type of info I need, I am not really looking for someone to give me a solution but to point me in the right direction for my research...  I am trying to avoid spending days on researching dead ends.