Filter a field as a portal

[ma39]

Hello everyone! 

 

I am a new french user FM and I just put my question here because you are the experts FMP: D I'm sorry if my comments are confused but English is not my native language and I am not bilingual.

 

That, in a nutshell: Can I filter a field as an external table is filtered? 

 

Let me explain, I work to show a billing amount based on its category and date, so I used a portal that displays a value (because I am the sum), but it does is not very practical, it's not very "clean" I think. So did I do? I will wish it to apply this filter, but in the section directly without going through a portal. 

 

Some people advised me to take a calculated by running a SQL query. 

 

Knowing that I should have 3000 calculated fields with SQL or with an portal. What should be preferred in terms of performance? What is the most reactive? But in these 3000 fields only about 500 find a value so it's more in queries. 

 

Here's a picture, it's just for the first line and the filter here: http://puu.sh/b4pkO/2b8dcf0bc1.png 

 

Left (invoice::FACT_DATE 4) = Left (exercise::l_prec_1;4) And  Left (Right (invoice::FACT_DATE;4); 2) = Right (exercise:: l_prec_1; 2) And prestation::prest_desc = "Account"

 

 

In any case I thank you in advance, I hope that my words are clear. All my tables are not stored on FMP but on a MySQL database (via ODBC driver for integration into FMP)

 

Thanks !

[comment]

I don't understand your question - and not because of your English. Can you explain what exactly is your purpose here? If all you want to do is:

 

I work to show a billing amount based on its category and date,

 

then a report summarized by category and date (or by date and category) would be the simplest and fastest method.

[Lee Smith]

Hi ma39, and welcome to the FM Forums,

 

Here's a picture, it's just for the first line and the filter here...

 

Please post your files here.

 

If you need assistance, just send me a Private Message.

[ma39]

First, thank you for your answers!

 

I don't understand your question - and not because of your English. Can you explain what exactly is your purpose here? If all you want to do is:

 

 

then a report summarized by category and date (or by date and category) would be the simplest and fastest method.

 

Yes, a report summarized by category and date is the simplest and fatest method. But I don't want this, I want a single cell as in Excel. 

So, I think I have two solutions :

 

My actual method :  I used a portal that displays a only value thanks to a filter

Or SQL Method : I create a non-stored calculated field and place ExecutSQL( MyFilter)

 

Who is the best and the more powerful ? Do you have other method ? 

[LaRetta]

Hi ma39,

 

Comment is the best person to assist you but a few things bother me and might explain why you are struggling to get the results you wish.  

 

1) Your fields all end with a number, i.e. d_prec_1, d_prec_2 etc which suggests your solution is not normalised.  Any time you see fields with following numbers, it should send red flags.

 

2) This date comparison suggests either that your 'date' fields are not date or that you are again (same as first problem) not normalised so cannot compare record dates to record dates:  Left (invoice::FACT_DATE 4) = Left (exercise::l_prec_1;4) And  Left (Right (invoice::FACT_DATE;4); 2) = Right (exercise:: l_prec_1; 2).

 

Whatever you do, I would suggest against using a calculation with ExecuteSQL() unless the table it resides in has very low number of records and is expected to always remain low.  Can you explain "Knowing that I should have 3000 calculated fields with SQL or with an portal."  Are these 'numbered' fields from an external source such as MySQL or are they in FileMaker tables?  3000 calculated fields is certainly not the direction you want to head.  Can you attach your FM file (zipped first)?

 

Also, as Michael asks, knowing the purpose would help a lot as well.  Why would a report not work for you?  How will the information be used please?

[comment]

 I want a single cell as in Excel. 

 

I am afraid I have no idea what that means.

[comment]

Is it possible you're looking for something like this?

http://fmforums.com/forum/topic/71836-getting-more-out-of-filtered-portals-in-version-11/

[ma39]

Thank you for your answers.

It's not easy to explain but I'll try to be clear. Unfortunately, I can not send you this file because everything is on a MySQL server, except that the calculations are not saved, nothing is saved on the software.

To explain the context, it is a database that is written by an accounting software, so I have to adapt to it and can not add fields as I want in the database. With FileMaker, I want to display data from a client, display its budget and be able to change a few numbers as the rate of increase.
We tried to do this via Excel by importing the data via a plugin but it was not as soft and could not change the data and this is reflected in the database. We could only view the data.
So I decided to pass on FileMaker Pro that I found more intuitive than Access.

That's the context.

So to date, I did that because the dates of the database are not in the same form. And I can do as I have just explained nothing. I have to adapt to the database and not the other way unfortunately. So to explain it to you in the database invoice is in the form: YYYYDDMM
when I compare it to a value in the form YYYY / MM

Now for the field, I'll try to explain. I have two tables, one for the bill and the other for the line item, it is connected by FACT_ID (see photo)
In this table there depending on the client code all those billings with the description. Simplifying with fake values:

'' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' ''
CLIENT_ID //// PRICE /// DESCR_CODE // FACT_ID
156 150 // // C10 // 15310
156 // 78 // D1 // 15311
156 // 10 // C10 // 15312
'' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' ''

'' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' ''
FACT_ID // DATE //
15310 // 20143105 //
15311 // 20143011 //
15311 // 20143012 //
'' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' ''

What I want is a field I have:

160 (150 + 10) So the sum custom, date and description (CLIENT_ID, DATE, DESCR_CODE)

Currently what I do:
I create a statistical field that total PRICE
Can I create a portal for each field with a filter.

 

I hope you better understand my problem.
And a big thank you for your help!

[ma39]

Is it possible you're looking for something like this?

http://fmforums.com/forum/topic/71836-getting-more-out-of-filtered-portals-in-version-11/

 

OMG.It's totally crazy!

 

Yes, that's exactly what I like!

One question, if there is no value for 2009 is that the column will be displayed? For I could wish that even if the field is empty it appears that all the months and Descr_code appears.

I will try to understand your file, but it looks complicated.

[comment]

I understand better what you have now - but not what you want.

 

As an aside: using Filemaker only as a front-end for a MySQL database is not a good idea.

[ma39]

What would you like me to use?
I think I'll put a few data (20) per client, but me is mainly to display values​​.

 

So I would like a table like in attachment, but with all the column and row even if there is no value to display. 

Do you understand better ? 

[comment]

What would you like me to use?

 

I don't know, but I am sure you will find plenty of choices if you search for "front-end for MySQL database".

 

 

A note about my cross-tab demo: as you can see in the linked thread, it's not meant to summarize large amounts of data.

 

Also keep in mind that no matter what technique you use, in Filemaker the number of columns displayed at any time is fixed in advance. You can change the contents of the displayed columns (simulating a horizontal scroll), but not add/remove columns dynamically.

 

 

On the positive side, if you are able to summarize the data using an SQL query, you can then display the result using a dummy table - simply by parsing the text returned by the query into rows and columns..

[ma39]

Yes I understand. But this is not a front-end I want. I will make a version of FileMaker Pro and if it is not powerful enough I would see.

But to return to your file. I can not select the data as I did. I do not really understand your filter in the portal.

I'll ask a French friend, it may be simpler. I'll let you know.

[ma39]

I'm sorry I have a second question Comment. 

What is the best between portal with a single value as I make and your table with your filter ? Who is the best and the more powerful ?

[comment]

I am afraid I cannot answer that because (a) I don't know what you mean by "portal with a single value as I make" and ( I don't know how exactly you're going to use this and with what data. There's no such thing as a best approach - only best approach in the circumstances.

 

In any case, if you're looking for performance then I would refer you to my first post in this thread.

[ma39]

(a) It's my method to display the Price with this filter in a portal : Left (invoice::FACT_DATE 4) = Left (exercise::l_prec_1;4) And  Left (Right (invoice::FACT_DATE;4); 2) = Right (exercise:: l_prec_1; 2) And prestation::prest_desc = "Account" 

 

( Per tab, because I display information with tab, so there is between 150 and 200 fields / values but 80 % are empty. But with all the tab we are at 1000 - 1200 values. 

 

Are there a difference in performance between a single tab that displays all values ​​and multiple tabs that displays 500 values ? 

[ma39]

For explain the two solution, I make a FileMaker file 

 

https://drive.google.com/file/d/0B0d29tvC5NukMEY1QTQ0eHBKS1E/edit?usp=sharing

 

If Comment can explain me in these explain file how your table works ? And how configure it ? 

 

Thanks !

comment.zip