1 2 Previous Next 16 Replies Latest reply on Jan 23, 2015 7:35 AM by siplus

    Display data from records based on date

    wfgclapp

      I have a simple table of invoice data. Screenshot below.

       

      I want to show on screen the fields: Sum_Sales, Sum_Profit, Sum_Cases

       

      I want the user to be able to enter in a to and from date and the data on screen change to match only the sums for invoices in that date range.

       

      I would also like user to enter a salesman number and see sums of invoices only associated with that salesman number.

       

      I don't want to use a portal, I just want to see those fields.

       

      Can anyone give me advice on the simplest way to do this?

       

      Thanks a bunch for any help.

       

        • 1. Re: Display data from records based on date
          jbante

          The simplest thing to do from a development point of view would be to add the Invoice_Date and Sales_Rep fields to your layout, and teach your users how to perform the finds.

          • 2. Re: Display data from records based on date
            wimdecorte

            But add the fields on a layout specific for this purpose.  Because otherwise every time they change the found set but are not interested in those numbers, FM will summarize all the data for nothing.  Can take a while and it will get very annoying very quickly

            • 3. Re: Display data from records based on date
              erolst

              The simplest thing for your users would be if you offered three global fields (gDateBegin, gDateEnd, gRepNum) and perform a scripted find based on these values.

               

              PS: There doesn't seem to be a primary key field in your field list …

              • 4. Re: Display data from records based on date
                siplus

                Invoice_Number ? 

                • 5. Re: Display data from records based on date
                  duncanbaker

                  Without getting too off topic from the original question, I never use user data as a primary key. In this instance there's no auto enter serial set up on the invoice number field (with unique value required etc), but even if there was, if I were building this system I'd have a field that never goes on any user layout that is either an auto enter serial or Get ( UUID ).

                   

                  As for the original question, I agree with all the above - Jeremy's will work by using the range operator "..." in the date field when performing the find. Mike's globals add a bit more work for you to implement but make it simpler for the users. And Wim's caution about summarized data and how this might slow down performance is one to carefully consider.

                   

                  Let us know if you need more help.

                  • 6. Re: Display data from records based on date
                    MicheleOlson

                    You could add global fields for start date,  end date and sales rep num.

                     

                    Have the user enter the start and end dates and sales rep number in the globals, then click a button (Show Totals) with script attached that finds the set of records (thus displaying the summary totals for the set). If it was only a single field it would be easiest to attach the script to the global as a script trigger that performed the find when the field was modified or exited. Put the globals in the header.

                     

                    HTH,

                     

                    Michele

                    • 7. Re: Display data from records based on date
                      erolst

                      siplus wrote:

                       

                      Invoice_Number ? 

                       

                      Aah, you mean that field without auto-enter, validate unique etc.?

                      • 8. Re: Display data from records based on date
                        erolst

                        duncanbaker wrote:

                        Mike's globals add a bit more work for you to implement but make it simpler for the users.

                         

                        My name is not Mike – “Oliver”, if you must.

                        • 9. Re: Display data from records based on date
                          duncanbaker

                          Ha ha. I'm sorry. No idea where that came from. Might just be a bit early in the day where I am. I'll take a quick snooze...

                          • 10. Re: Display data from records based on date
                            wfgclapp

                            wimdecorte wrote:

                             

                            But add the fields on a layout specific for this purpose.  Because otherwise every time they change the found set but are not interested in those numbers, FM will summarize all the data for nothing.  Can take a while and it will get very annoying very quickly

                             

                            Not sure I follow. If they do something to change the found set it should mean they WANT to see the numbers change, right? I guess you're saying I might have something ELSE going on with found sets that doesn't have to do with showing summary data?

                            • 11. Re: Display data from records based on date
                              wfgclapp

                              erolst wrote:

                               

                              The simplest thing for your users would be if you offered three global fields (gDateBegin, gDateEnd, gRepNum) and perform a scripted find based on these values.

                               

                              PS: There doesn't seem to be a primary key field in your field list …

                              I'll try this.

                               

                              The invoice number is my primary key, it just isn't shown as indexed. That is actually a dummy table I created for the purpose of this post. The real table has bunch more stuff in it and I wanted to be clear.

                              • 12. Re: Display data from records based on date
                                wfgclapp

                                duncanbaker wrote:

                                 

                                Without getting too off topic from the original question, I never use user data as a primary key. In this instance there's no auto enter serial set up on the invoice number field (with unique value required etc), but even if there was, if I were building this system I'd have a field that never goes on any user layout that is either an auto enter serial or Get ( UUID ).

                                 

                                As for the original question, I agree with all the above - Jeremy's will work by using the range operator "..." in the date field when performing the find. Mike's globals add a bit more work for you to implement but make it simpler for the users. And Wim's caution about summarized data and how this might slow down performance is one to carefully consider.

                                 

                                Let us know if you need more help.

                                Thanks. I think the globals will work best so I'm going to try that.

                                • 13. Re: Display data from records based on date
                                  wfgclapp

                                  Thank you everyone for responding. I'm going to try some of this out this afternoon and will be sure to post back.

                                  • 14. Re: Display data from records based on date
                                    wimdecorte

                                    wfgclapp wrote:

                                     

                                    wimdecorte wrote:

                                     

                                    But add the fields on a layout specific for this purpose.  Because otherwise every time they change the found set but are not interested in those numbers, FM will summarize all the data for nothing.  Can take a while and it will get very annoying very quickly

                                     

                                    Not sure I follow. If they do something to change the found set it should mean they WANT to see the numbers change, right? I guess you're saying I might have something ELSE going on with found sets that doesn't have to do with showing summary data?

                                     

                                    Yes.  If you add the summary fields to a layout where the user does a lot of work in but is not interested in seeing the summary data then every time they do something that changes the found set, the summary data will get calculated and it will get in the way of their other work.

                                    1 2 Previous Next