Filtering Records/Data From Multiple Tables - Multi-criteria For Charting Etc.

[GisMo]

I am trying to chart and analyze sale data. In particular, "Most popular product(from a specific product category) this quarter/month/year/etc" and things of that nature.

 

In this database there are several tables: I will try to explain how they are linked

 

Sales Order

-Sales Order Line Item

--Product

 

Product Category

-Product

--Sales Order Line Item

 

 

For example, I want to see the most popular product during a certain fiscal quarter do do so, I need to filter the line items by the following criteria and then have a calculation of the sum of found line items grouped by product * order quantity:

 

Based on the tables this criteria needs to be met:

SalesOrder::OrderStatus /= "Open" (not equal) (i.e. closed/completed orders)

SalesOrder::Year=CurrentYear

SalesOrder::Quarter = Fiscal Quarter(or can be month or can be omitted entirely)

Product::Category="my category"(whatever)

 

I've tried many solutions and getting somewhat close, but I get stuck with Un-Index-able match fields(keys) dues to calculations from related tables or circular references. Any tips would be great. 

 

 

Lastly, I think I can accomplish the same thing with a script and perform multiple searches against the LineItems based on related tables, but wanted to know if there is a more direct way via the relationship graph.

 

I'm happy to provide more details if needed.

 

Thanks in advance.

[No_access]

I think this is close to what you are looking for, heres an old post of mine

 

http://fmforums.com/forum/topic/88037-how-to-summarize-revenue-per-customer-by-product-type/?p=404350

 

I this this is probably what you want to do, if you can make a sample file I would be glad to help.

[GisMo]

Thanks for the reply. I think the solution in your other thread is a scripted approach of capturing the data. I was looking for a more direct method via manipulating the relationships and table occurrences.

 

I'm still looking for a viable method, but I have been testing a scripted method in the mean time.

[Fenton]

Some one may upload a file which has this, done via calculations and relationship, but I'll just give you some tips (and I don't have such a file, that I can easily find :-).

 

Yes, it can be done via the above. There are a few rules via such. Some of these you likely know, as your skill level is expert; but I'll write some anyway.

 

A. Calculation fields can have a result which can be "Indexed," or else is "Unstored." One which can be "Indexed" can be set as either. But if it cannot be "Indexed" it will be "Unstored." 

The "left" side (from) of a relationship can use fields which are either. However the "right" side (to, targeted), must use only Indexed fields.

 

 

B. Anything which calculations uses such things as "Get (CurrentDate)" must be Unstored, as "current" requires that. So it can only be used on the "left" side of a relationship. Example for field "_cFinancialYear_curr", result number (beginning on September):

 

Case ( 

Month ( Get ( CurrentDate )) > 8; Year ( Get ( CurrentDate )) + 1; 

Year ( Get ( CurrentDate ))

)

 

For an "Indexed" field, in the table where the data (change the "date" field to one you're using) is, "_cFinancialYear", result number:

 

Case ( 

Month ( DatePlaced ) > 8; Year ( DatePlaced ) + 1; 

Year ( DatePlaced )

)

 

 

Quarter:

 

_cFinancialYear_Q_current

 

Case ( 

Month ( Get ( CurrentDate ) ) > 8; Year ( Get ( CurrentDate ) ) + 1; Year ( Get ( CurrentDate ) )

) & 

Case ( 

Get (CurrentDate)  ≥ Date ( 9 ; 1 ; Year (Get (CurrentDate)) ) and Get (CurrentDate) < Date ( 12; 1 ; Year (Get (CurrentDate)) ); 1;

Get (CurrentDate)  ≥ Date ( 12 ; 1 ; Year (Get (CurrentDate)) ) or Get (CurrentDate) < Date ( 3; 1 ; Year (Get (CurrentDate)) ); 2;

Get (CurrentDate)  ≥ Date ( 3 ; 1 ; Year (Get (CurrentDate)) ) and Get (CurrentDate) < Date ( 6; 1 ; Year (Get (CurrentDate)) ); 3;

Get (CurrentDate)  ≥ Date ( 6 ; 1 ; Year (Get (CurrentDate)) ) and Get (CurrentDate) < Date ( 9; 1 ; Year (Get (CurrentDate)) ); 4

)

 

 

I did the Quarter calculation field, but using the above for Year, and adding a field for Quarter, which is:

 

Field: _cQuarter

 

Case ( 

DatePlaced  ≥ Date ( 9 ; 1 ; Year (DatePlaced) ) and DatePlaced < Date ( 12; 1 ; Year (DatePlaced) ); 1;

DatePlaced  ≥ Date ( 12 ; 1 ; Year (DatePlaced) ) or DatePlaced < Date ( 3; 1 ; Year (DatePlaced) ); 2;

DatePlaced  ≥ Date ( 3 ; 1 ; Year (DatePlaced) ) and DatePlaced < Date ( 6; 1 ; Year (DatePlaced) ); 3;

DatePlaced  ≥ Date ( 6 ; 1 ; Year (DatePlaced) ) and DatePlaced < Date ( 9; 1 ; Year (DatePlaced) ); 4

)

 

Now, _cFinancialYear_Q is just:

 

_cFinancialYear & _cQuarter

 

 

C. Another trick which I use many times which need to see whether something is "true," about some calculation is that I can a "flag" result (also called "Boolean"); the result being 1 if so (nothing otherwise). Example field, "_cOpen_flag";

Case ( IsEmpty (OrderStatus, 1)

 

Then, in many tables, I have a calculation field, "_c1" whose calculation is: 1

That is, there is a 1 in every record. It can be "Indexed"

 

The "_c1" field can be used in the right side of any relationship which wants to target a "flag" field on the right; in this case "_cOpen_flag"

 

 

D. I think you would have to do the different "date" (actually numbers) to view as different relationships. I cannot see how to do a change to see in the same portal. I would think different relationships, different portals, viewed within different Tabs. It would work because a "financial year" and "financial quarter" and "month" do not really argue with each other. You could flip between them.

 

 

E. Product::Category="my category" (whatever) 

 

This would work in a relationship, because it is targeting a text field. One the left side of the relationship could be a global field (drop-down list, or whatever). 

 

 

F. Because the "right" side of the relationship are all Unstored fields, not attached to any Indexed field one a calculation, the "right" side could be from any table/layout you want it from.

 

P.S. I am a little tired, I will send this as is :-|