7 Replies Latest reply on Nov 29, 2011 2:08 PM by philmodjunk

    Mostrar los registros sin repetirlos

    megacop

      Title

      Mostrar los registros sin repetirlos

      Post

      Necesito una colaboración mil gracias. De un informe de ventas necesito mostrar los clientes en un archivo .tab pero quiero que me muestre todos los clientes sin repertirlos ya que cada cliente tiene más de 10 facturas y lo muestra diez veces y el nombre del archivo debe cambiar de acuerdo al mes y año el nombre es: 112011clientes

      Gracias por la colaboración.

        • 1. Re: Mostrar los registros sin repetirlos
          philmodjunk

          Hola, No hablo español, pero intentado usando un traductor de la tela entender su poste. No trabajé muy bien sino que pienso que usted tiene clientes y las facturas y usted necesitar un informe que enumere a cada cliente un solo rato. No puedo decir realmente más que eso de su poste.

          ¿Qué tablas usted tiene?

          ¿Cómo se relacionan?

          ¿Por qué está el nombre del archivo (12011clientes) importante?

          • 2. Re: Mostrar los registros sin repetirlos
            megacop

            Let's see how I go with the English.

            I need a help many thanks. When you export sales report in a file "tab" I want to show all business customers who have shopping once a month each, every customer has more than 10 bills and therefore presents more than ten times.

            The report comes from "Bill" that takes data from the tables: invoices, customers, products,related directly to the invoice table.

            As would be the script for the export file name changes according to month and year, the namefor November and December is 112011customers 122011customers and 012013customersJanuary.

            Thank you for your help.

            • 3. Re: Mostrar los registros sin repetirlos
              philmodjunk

              I take it you want a monthly export of specific data in a tab file. Naming the file will be a simple thing to do.

              How will the user select the particular month for a given export of this data? Should it be scheduled to happen automatically once every month or will the user select the year and month they want and then perform the script to export the data?

              With either method, if it's a list of customers that you want to export, you should export from a layout based on teh customers table instead of invoices. You can perform a find on the customers table that specifies an Invoice date in the related invoices table in a specified date range such as: 1/11/2011...30/11/2011 (I'm using DDMMYYYY date format here)

              That will find all customers with at least one invoice dated fort the Month of November, 2011 and then you can export the data from this found set to your tab file.

              The filename can be set to a variable that is then used with your export records to export your data:

              Set Variable [$FilePath ; value: "File:" & Get ( DesktopPath) & Right ( "00" & Month ( INvoice::InvoiceDate) ; 2 ) & Year(Invoice::InvoiceDate ) & "Customers.tab"]

              Then type in $FilePath in the file reference dialog of your export records script step.

              (This example exports the data to your desktop, you can use other expressions to export to different locations on your computer. I've used my own field names, you'll need to use the names you actually have in your database tables.)

              • 4. Re: Mostrar los registros sin repetirlos
                megacop

                Thank you very muchfor your help, and when you exportsales reportin a file"tab"I want to showallbusiness customerswho haveshoppingoncea montheach,every customer hasmore than 10billsand thereforepresentsmore than tentimes.

                Consider this example:

                BERLIN                                                                                         2201 Kilos 1 4

                BERLIN                                                                                         2201 Kilos 1 4

                BERLIN                                                                                         2201 Kilos 1 4

                Miguel A. Vivas C. N 18030-0 2737739 Corabastos Bodega 7 Local 1 2201 Kilos 1 4

                Miguel A. Vivas C. N 18030-0 2737739 Corabastos Bodega 7 Local 1 2201 Kilos 1 4

                Miguel A. Vivas C. N 18030-0 2737739 Corabastos Bodega 7 Local 1 2201 Kilos 1 4

                Miguel A. Vivas C. N 18030-0 2737739 Corabastos Bodega 7 Local 1 2201 Kilos 1 4

                Miguel A. Vivas C. N 18030-0 2737739 Corabastos Bodega 7 Local 1 2201 Kilos 1 4

                Miguel A. Vivas C. N 18030-0 2737739 Corabastos Bodega 7 Local 1 2201 Kilos 1 4

                Miguel A. Vivas C. N 18030-0 2737739 Corabastos Bodega 7 Local 1 2201 Kilos 1 4

                Miguel A. Vivas C. N 18030-0 2737739 Corabastos Bodega 7 Local 1 2201 Kilos 1 4

                Miguel A. Vivas C. N 18030-0 2737739 Corabastos Bodega 7 Local 1 2201 Kilos 1 4

                • 5. Re: Mostrar los registros sin repetirlos
                  philmodjunk

                  From your example, I can see the same customer listed repeatedly with what appears to be data from the  invoices table. Is this what you want?

                  Exporting should then be done from the invoices table and you can include fields from the related customers table in your export.

                  Do you know how to do this without a script using export records in the File menu?

                  Do you know how to use Export records in a script?

                  Have you created scripts in FileMaker before?

                  • 6. Re: Mostrar los registros sin repetirlos
                    megacop

                    Thank you very muchfor your help.

                    I know how to do this without a script using export records in the File menu.

                    I don´t know how to use Export records in a script.

                    I createdscriptsbut simpleor basic.

                    Inthe images thatyou seeAnnexrepeatedthe clientand it is fromthis window thatIcreate andexport onlythe list of customers.Withexport toexcelbuttoncreate a script toexport butthe file namewould be the sameforever.

                    • 7. Re: Mostrar los registros sin repetirlos
                      philmodjunk

                      Do you want one row per customer or one row per invoice? I first thought you wanted one row per customer, then one row for each invoice, and now I am not sure.

                      I've already described how a scripted export can save to a specified file name so that each file produced can have a different file name. this works for exporting to excel, or one of the txt files such as tab or csv. I guessed at a date field, InvoiceDate, but you can refer to any accessible date field or use Get ( CurrentDate ) instead to produce the desired file name. It also exports with "tab" as the file extension as that is what you original requested. You can specify a different extension if you choose to export this data as and excel file instead.

                      I asked about a manual export because setting this up in a script uses many of the same dialogs so I won't need to explain that part as much.

                      Here's the basic script you would use. The key details that you choose will control what data is exported and in what file format, but the actual script steps are the same:

                      Set Variable [$FilePath ; value: "File:" & Get ( DesktopPath) & Right ( "00" & Month ( INvoice::InvoiceDate) ; 2 ) & Year(Invoice::InvoiceDate ) & "Customers.tab"]
                      Export Records [no dialog ; “$FilePath” ; Add ; Windows ANSI ]

                      In the script editor, when you select Export Records, you'll find two checkboxes: Specify Data Source and Specify Import Order. Click the first one to open the specify file dialog and enter the name of your variable (without the quotes) in this dialog. Then click the second check box. This will bring up the same dialog boxes you use when choosing Export Records from the File Menu so you should be able to specify the options you want for your export here.