6 Replies Latest reply on Sep 17, 2015 1:40 PM by dtcgnet

    Report that shows records more than once.


      I don't even know if what I'm wanting is possible, but here it goes:


      I have an orders table.  For each order, we have a quote - sometimes several quotes- depending on the number of revisions.


      My boss wants a weekly report showing what was typed that week.  If it was just one quote, I'd know how to do it, but the multiple revisions are stumping me.  For instance, each order table has the possibility of up to 10 revisions.


      Is there a way to have the Original appear under it's date, while Rev1 appears under it's date on the same report?  I'd like to view it in list view, sorted by the week that it was typed.


      Right now, I have check boxes for each revision where the receptionist can check off when she's typed an original/revision.  When she clicks the appropriate check box, I have a script trigger that gets the current date and saves it in a field (i.e. TypedContractOriginalDate, TypedContractRev1Date, etc.).


      Hopefully, this makes some sense.


      Thanks in advance for your help.

        • 1. Re: Report that shows records more than once.

          I'm not 100% certain based on your description, but it sounds like you have a data modeling issue.


          You should have two tables for this, one for Orders and one for Quotes. Each quote should be a separate record in the Quotes table, tied to Orders via an Order ID (perhaps using a UUID as a key field). Then, you can easily produce whatever quote you want based on revision or date from a report based on the Quotes table.


          If that's not your issue, please provide more detail.



          • 2. Re: Report that shows records more than once.

            Your database isn't really normalized. What would be better would be to parse "revisions" out to a separate table, this way you could create a record for each revision (ergo making pretty much unlimited revision capacity), instead of storing those as fields.


            Then your report could be based on the revisions table, sorted by Job, and filtered by date range. This would give you a row for each revision.


            To do what you want within the structure you setup, you'd have to create a virtual list report or similar.

            • 3. Re: Report that shows records more than once.

              In an FM layout, one record = one row.


              You should probably consider quotes, or at least their revision dates, being in a separate table, so you could base the report layout on that table. Aside from the current reporting needs, I'd caution that any field that ends up with incremental copies (original, rev1, rev2) should make you stop and at least think about adding another table. Without more details, I can't be more specific, but hopefully this gives you an idea.


              Another possibility, for showing the same record multiple times in a report/portal, is a virtual list, where you could gather up the values from your various orders in a text field, and then base a report on virtual list records that reflect the values from the text field. If you're not familiar with the virtual list method, you might just google that to get more guidance than would be practical here.


              Hope this helps, or at least gives you some ideas.


              Chris Cain


              • 4. Re: Report that shows records more than once.

                I appreciate all of your responses.  I finally got time to work on this again.


                So, I made a new table called "TypedContracts".  I related the "Orders" table's pk w/ an fk on the "TypedContracts" table. The "TypedContracts" table has three fields: __TypedContractspk, _Ordersfk, and TypedContractsCreatedDate.


                I have the checklist on the "Orders" table w/ a script trigger that once it's modified, it goes to the "TypedContracts" table, makes a new record, and then comes back to the "Orders" table.


                The problem I'm having is that the new record that's created doesn't seem to "relate" to the specific order from where the checkbox was clicked.


                I have several of these relationships throughout the database, that work fine, so I'm a little lost as to why this one isn't working.


                Thanks for any help.

                • 5. Re: Report that shows records more than once.

                  You'll need to populate _Ordersfk when you create the new record in order for it to show up. That needs to be part of your script.

                  • 6. Re: Report that shows records more than once.

                    In the script that's triggered, set a variable ($OrdersPK) equal to the PK of the order you're looking at. When you create the new TypedContracts record, your script should then say:


                    Set Field TypedContracts::_Ordersfk = $OrdersPK


                    That'll ensure that the new record's FK matches up with the original order's PK.