Create a report from a dropdown list

[Quant]

Hi,

I would like to create a report from each individual customer, and then see all the items he ordered.

The customer should be selected from a dropdownlist, and when de customer is selected the report is being shown.

I have a table customers and a table Sales, the relationship is the contactpersonID.

Can somebody help me to make a start.

Thanks,

Frans

[LaRetta]

Hi Frans,

To isolate all of a specific Customer's sales, and since you already have a relationship in place, all you need to do is script similar to:

Set Error Capture [ On ]

Go To Related Record [ Show only related records ; From Sales ; Match current record only ]

If [ Get ( LastError ) ]

Show Custom Dialog [ "No sales for this customer" ]

Else

... do whatever you wish because you are now on your Sales report form

End If

:smile:

[LaRetta]

You can also include a custom dialog and ask User whether they want sales for one customer, sales for all customers in the found set or to cancel. Pseudo-script might look like this:

Show Custom Dialog [ "Show Sales for which customers?" ; "One" ; "All" ; "Cancel" ]

If [ Get ( LastMessageChoice ) = 3 //user cancels ]

Halt Script

Else If [ Get ( LastMessageChoice ) = 2 // user selects all customers ]

Go To Related Records [ Show only related records ; from Sales ; Match found set ]

Else

Go To Related Records [ Show only related records ; from Sales ; Match current record ]

End If

Include error trapping.

Edited January 26, 2012 by LaRetta

[LaRetta]

Oh Frans, in truth I jumped the gun. Where are you when you want to select from the dropdown? If you are the Customer's table then no drop-down is required and what I suggested will work. You then create a report in the Sales table, with leading part based upon contactpersonID.

If you are on a different base table and, as you say you select the customer from a drop-down, then it will need to be attached to a global text field. Script would then switch to a layout based upon Sales and perform a find using the global field's value then switch to the Sales report layout.

Now ... if I have totally confused you, raise your hand.

And I apologize for a sloppy post. :^)

[Quant]

Hi LaRetta,

Thanks for your reply.

I have made a script as you mentioned, i made a form with a dropdownbox and attached the script to that box with a scriptrigger (onObjectModify).

The field in the dropdownbox is attached to the table customers.

Now i see the customers in the box and can choose them, but when i look in the listview or tableview i see always 4 records with the name of the

customer nomatter which one i choose what is wrong.

How do i attache the report tho this result...??

Thanks

Frans

[LaRetta]

Hi Frans,

• Is the drop-down in Customers a global field?
  
• Do you want to always find sales for the existing customer you are on? If so, no drop-down is required.
  
• Or do you want to select customers at random? This would mean drop-down is necessary.
  
• Do you ever want to view a found set of customers and create a report for each of them, so that it looks like:
  

Acme Products

3/15/2010 . Inv 122 $350

4/3/2010 . Inv 165 $200

Total for Acme - $550

(possibly page break)

GGC Corp

5/11/2010 . Inv 188 $50

8/1/2010 . Inv 199 $10

Total for GGC - $60

when i look in the listview or tableview i see always 4 records with the name of the

customer nomatter which one i choose what is wrong.

That is because, as explained, you do not need the dropdown if you want to find sales on the customer record you are currently on. The scripts I provided use the current customer record(s) and not a drop-down.

This is why I need to understand the top four bullets listed. And why I confessed to jumping the gun.

[Quant]

Hi LaRetta,

Thanks for your reply.

The answers to your questions are:

1e. The dropdown is not a global field.

2e. No when there are no items no problem.

3e. I want to select customers at random.

4e. I want only the result from one customer.

Thanks,

Frans.

[LaRetta]

Thank you for your perfect response. You need to use a global field and attach your drop-down to it. But a drop-down is not the proper control (selection device) when there are more than a few dozen choices. It would work best to provide your Users with a regular global text field (no drop-down attached) and let them type in part of the customer information (such as name). Script would then just perform a find on the field(s).

Can you describe why you want this process? You mention 'when there are no items no problem' . Are you looking for specific types of customers only? If so, you can use a conditional value list to filter the drop-down to only 'problem' customers from which to select. We need a better idea of the context and purpose, if you would be so kind.

And welcome to FMForums, by the way. :^)

[Quant]

Hi LaRetta,

Thank you for your welcome to me,

Now the answer to your questions:

I want in this proces to see what each customer buys seperately, so i can see the differance between this year and 2011. These are no problem customers but i would like to see

if they are one the same level as 2011.

The number of customers is not verry high less then 25 so that is no problem with a list to choose from.

Thanks,

Frans

[LaRetta]

Then you need to create a global text field (call it gContactID ) and attach your dropdown to that. The value list should be based upon the contactpersonID.(primary key) also showing values from second field FullName. It should display their name for selection but it inserts the ID into the global field. OnObjectModify (as you had it) should run this script:

Freeze Window

Go To Layout [ layout based upon Sales ]

Enter Find Mode [ uncheck pause]

Set Field [ Sales::contactpersonID ; gContactID ]

Set Error Capture [ On ]

Perform Find [ ]

If [ Get ( LastError ) ]

Show Custom Dialog [ "no Sales records found" ]

Show All Records

Go To Layout [ original layout ]

End If

... records found so switch to your report

Go to Layout [ Sales Report ]

But if you want two years' worth then you will need to include the date in the search. You can them summarize your report by year (or even remove the body completely and just have a count summary (from Sales) up in the header. If you would like assistance with this, let me know. :~)

[LaRetta]

Are you sure you set gContactID to be a global field? Did you go into the field's Options and the Storage tab and check 'use global storage'?

You will need to create an empty clone of your file, zip it and attach it to a post here. If you are uncomfortable posting it here, you can PM it to me.

We'll figure it out ... not to worry. :^)

[Quant]

Hi LaRetta,

The solution works perfect, i made a mistake in the "values from" i did not match the right fields.

I have another question, now i see the ID in the dropdownlist at the top, is there a way to prevent this?

And when i return to my layout i have to push the button "show all" , i gues that i can do this in the way

back from the report layout with a command.

Many thanks for your help.

Frans

[LaRetta]

Hi Frans,

Drop-downs, after selection, always show the ID instead of the (customer) name. Pop-ups will still display the name after leaving the field. When selecting IDs, it is best to use a pop-up which protects from invalid entries being added. However, since this is attached to a global field, a drop-down is fine. Do you mean that you want the field to blank itself when you return to the customer layout? You can blank it right where I put the comment '... records found so switch to your report". Just add a script-step of: Set Field [ gContactID ; "" ].

I am not sure I understand about wanting to 'show all' from the original customer layout. What if your User is working with a found set of Customers and, once they view a single Customer's sales and are returned, want to remain on the same found set? Could you say a bit more about this piece?

ADDED: So in other words, you can clear the global immediately after you are done with it. Sometimes a User might want to see the value of the last customer they looked up, thus you can leave that value. It is unique to THAT User (that is the benefit of global fields). Choice is yours.

Edited January 27, 2012 by LaRetta

[Quant]

Hi LaRetta,

To answer your question about "Show all", is just for me to see all the records and not only a subset.

I have another question about the dropdownbox, is it possible that when i choose an customer, a second choice can be made also in a dropdownbox...??

This second choice could be:

1e Salesreport customer

2e Salesreport all customers

3e Outstanding payments

4e Credit note

Thanks,

Frans

[LaRetta]

I am afraid I do not understand, Frans. If I understand, your first drop-down is on a customer layout and it pops all customers for selection. If you now want a second drop-down, it would make sense for all items except 2e (Salesreport all customers), ie, why select a customer then select 'all customers' in the next drop-down?

It sounds like you might benefit from sending Users to a Reports menu where they can specify what they want and then hit a button?

[Quant]

Hi LaRetta,

Yes, your right the second one is not an item that should be in the list (stupid from me) but your statement in the last sentence is correct.

It would be nice if i could choose wich report also from a dropdownbox.

Thanks,

Frans

[Quant]

Hi LaRetta,

I have one more question about the above topic,

The solution works perfect, but i can't get the right data in the report.

The problem is that i only see the first entry from the customers order, i have tried to get the orders from

another table (Artikelen), that works but then i cannot see the order from one customer but all.

The link between the databases is the FactuurID.

Thanks,

Frans

[LaRetta]

I would need to see the file, Frans. And I am tied up today. If others can't assist, I will try to get help tomorrow. :^)

[Quant]

_{Hi LaRetta,}

_{I finally figured it out.}

_Thanks,

_Frans