1 2 Previous Next 19 Replies Latest reply on Apr 25, 2014 3:20 PM by TBond

    Using Summary Information for New Records

    TBond

      Title

      Using Summary Information for New Records

      Post

           I have a table with a large quantity of records with various "Sign Types". I need to take the summary information from this table and turn it into separate records for use in set of ordering forms. I can do a sub-summary which returns "Sign Type A has a quantity of 10" "Sign Type B has a quantity of 6" but I need to then further modify that information.

           For example, I may have a quantity of 10, but need to manually increase the pricing count to 12 to cover future changes. If it was a field, I could do "Count + Additional" but if I do this as a sub-summary, then my layout still has hundreds of records and there's not a simple place to add the extra 2.

           Sorry this is vague. Any suggestions?

        • 1. Re: Using Summary Information for New Records
          philmodjunk

               It is vague and since details are what are critical, it makes it hard to suggest much. There are a variety of possible solutions from adding a record just for the purpose of adding that extra quantity, to calculation fields that combine the computed total with an "adjustment" field to scripts that take the summarized totals and create a new record in a different table.

               But it's impossible at this point to recommend one over the other though "creating a record in another table" would be an option I would not use except if I had no practical alternative.

          • 2. Re: Using Summary Information for New Records
            TBond

                 Here is a screen shot for example. The image on the left is the preview of the bid form with a sub-summary section sorted by "Sign Type." It gives me a list of the unique sign types and quantities, then calculates with extended cost fields. I would love to have something like this where I could put a "Additional Quantity" field to manually bump up the quantities.

                 The problem I have is that when I go to enter additional information, I have to go back to the records like the right image. I could put the "Additional Quantity" on this record, but there may be hundreds of other records so how would someone know which record I added it to?

                 I don't want to manually add records to bump up the count, since those extras mess up our workflow. I'm open to other options, but the quantity input method must be simple enough for less-computer-savy workers to use.

            • 3. Re: Using Summary Information for New Records
              philmodjunk

                   I'm not sure that I understand what I see here. Is each row in the preview on the left summarized data from your sub summary part? Then what are the fields that look like data entry edit boxes  shown on the right? Are they also summary fields just left blank for this demo? And are both screen shots of layouts based on the same table?

                   In point of fact, you can add additional records and keep them from affecting other parts of your system if you label them for what they are and make sure that other parts of your system exclude them automatically. There are ways to automatically constrain found sets and to filter portals etc. to do that.

                   But you could also define a Qty adjustment field in this same table. You can then define a calculation field that adds the original quantity and the adjustment fields. you can then summarize this field to get a total that includes the adjustment while your original summary field will compute the total without this adjustment. As long as you enter this adjustment into any one record of the correct grouping, a summary field that summarizes the adjustment field will show it on the left hand layout.

              • 4. Re: Using Summary Information for New Records
                TBond

                     Both are from the same layout. The picture on the left above is what I see when I do a preview. This is what I would like my final report to look like. I would also like to be able to type in the Unit Cost and Extended Cost.

                     In my table, I have one layout by item number (not shown) that goes into detail about each sign with sign type, message, etc. The image on the right in the first attachment was a second layout just focusing on the sign type. I have a sub-section summary when sorted by sign type to give me a quantity. The Unit Cost and Extended Cost are basically blanks on this form. I've attached a layout screen shot that might help you.

                • 5. Re: Using Summary Information for New Records
                  philmodjunk

                       You don't have to preview the layout to see that result. You can just select list view and sort your records to get the same look, but now you are in browse mode. This matches my assumptions for the two different solutions that I offed in my last post.

                  • 6. Re: Using Summary Information for New Records
                    TBond

                         I like the idea of a quantity adjustment field, but I would like that to be on this form instead of somewhere in the records. I need to go down the list of sign types and say "Add 10 to this sign type, 3 to that one, and 15 to that one."

                         It's almost like I need something that converts the summary information into new records. Record 1 says "Ext.01" "Quantity 3". Then I could easily have another field that says "Additional." And in the future, I could use these records in the Bid Analysis with "Bidder 1 Cost" "Bidder 2 Cost".

                    • 7. Re: Using Summary Information for New Records
                      philmodjunk

                           "on this form" is exactly what I had in mind.

                           But what you really need is a related table of sign types so that you can specify all of this information once for each sign type. This is how you should specify the unit cost as well. With a related table of such sign types, you only need to specify this information once for each sign type.

                      • 8. Re: Using Summary Information for New Records
                        TBond

                             I'm still learning how related tables work. How do I turn the summary results from one table into individual records on the second table?

                        • 9. Re: Using Summary Information for New Records
                          philmodjunk

                               You wouldn't. You just need one record for each sign type. If you have just a few, you can create the records in that table manually. For larger numbers of records, you can set a "unique values, Validate always" set of field options on the signType field in your new table and import all records from the current table. The validation options will filter out duplicate sign types and leave you with one record for each sign type.

                               You'd use other fields in this table to set things like a Unit cost so that you only need specify it once. This data is then accessible to all records in your original file of the same sign type if you link the correct match fields in a relationship for these two tables.

                          • 10. Re: Using Summary Information for New Records
                            TBond

                                 Thanks, I'll see if I can put that together.

                            • 11. Re: Using Summary Information for New Records
                              TBond

                                   Ok, I've spent some time trying to educate myself about related tables and I would like to try the "unique values, Validate always" option you mentioned for importing records from one table to another.

                                   Could you point me in the direction of how I do that? Sorry for the long delay.

                              • 12. Re: Using Summary Information for New Records
                                philmodjunk

                                     As I recall, you want one record for each "sign type", correct?

                                     So in your new table, you define a field for sign type. Define any additional fields that you need for data you want to import along with the sign types. I'd include an Id field that auto-enters a serial number as one of the fields in this new table.

                                     Open field options for the SignType field and select the Unique Values, validate always options. Then go to your layout of your original table and do a show all records. Return to the layout for your new table and select Import Records | File. This opens a dialog for selecting first the file, then the table from which you want to import data. Use this dialog to select the very file you already have open and then use the import mapping dialog to align fields in the new table with fields in the old table as needed.

                                     Click the Import button and select the check box in the small dialog that pops up that enables auto-enter options so that each record in your new table will automatically get a value in that ID field that i recommended.

                                     That should then produce a set of records in your new table with one record for each sign type.

                                • 13. Re: Using Summary Information for New Records
                                  TBond

                                       Ok, that's exactly what I needed.

                                       Is there a way to automate the "import from self" step into a script so a user could run the script without having to browse to the file? I'm trying to make this a foolproof as possible, knowing some of the people in my office.

                                  • 14. Re: Using Summary Information for New Records
                                    philmodjunk

                                         I would recommend that you modify your design so that such an import is unnecessary, but all the steps I described can be steps in a script.

                                    1 2 Previous Next