Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

This topic is 5003 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

Hi,

How to overcome the problem “The calculation cannot be store or indexed because if reference a related field, a summary field, an unstored calculation field ..”?

I created an application, where the frontend user (kiosk mode) can choose which records he can see (config). He will be able to choose the data (begin and end), and if the record have yes in one field. I have done this with a script (goto).

In order to do statistics i created another field (x_select) to perform stats only related with special criteria, showed in config layout (these fields are global).

It isn't necessary to have multiple fields in the records to do statistics, because most users only do this time to time. So for a better performance I have created another tablet, just for statistics.

Now the problem, so I just want one tablet to do all statistics related with the records found (x_select), like the criteria show above:

To do this, I found three ways, but they didn’t work:

1. First way: On layout (stats), I created a field call stats::_kp_select field, the main idea is to relate this with sadpc00::x_select, in other to show a list of all values (sadpc00::admittied).

So, when stats::_kf_select is equal to sadpc00::x_select, this show do a list with related records.

But, this don’t work. I think the problem is related with x_select field, because it is a calculation field with global data. And it isn’t possible to make relations with global data into calculation? I can’t indexing that field.

2. Second way, didn’t work either.

Use a script trigger (on object modify) when the (sadpc00::x_select) change, they set the field (sadpc00:x_estat) with the value of sadpc00::x_select, then use this new field to do the relationship, in other to list only related values.

3. Third way, and last idea. Use a custom function (CombineValues ( list1 ; list2 )), to a list from sadpc00::admitted) and the second list sadpc00::x_select. And a relationship (x).

Then perform other function to select only the x_select values related with the criteria.

If ( ( ValueCount ( list1 ) = 0 ) or ( ValueCount ( list2 ) = 0 ) ; "";

GetValue( list1 ; 1 ) & "=" & GetValue( list2 ; 1 ) & ¶ &

CombineValues (

RightValues ( list1 ; ValueCount ( list1 ) - 1 )

;

RightValues ( list2 ; ValueCount ( list2 ) - 1 )

)

)

The last solution, didn’t work because I just get the first record, so I need to create a script with a loop.

But, there must be a simple way to do this, with a better performance.

Any ideas? Thanks

Thanks for your support,

test4.zip

Posted

Hey capsprojectos;

I'm a little confused with what you're trying to accomplish here.

If I've read into this properly, the end result you're looking for is to present the end-user with the information that matches various criteria that they can specify. If this is the case, then perhaps things can be simplified a bit. What I'm thinking is that maybe you could have the filter criteria on the same page as the actual data that they're going to view (ie a list view). If this is an option for you, then you can simply write a script (or various scripts) that will fire whenever the values in your filter fields (at the top of the list view) have their values saved/modified/whatever. The simple way to write a find script within FileMaker usually involves:

Enter Find Mode

Set Field {

Set Field { It's in these steps where you actually set the field that you want to search on with the values that were specified in the user's filter criteria }

Set Field {

Perform Find

Hopefully I've understood what you're trying to accomplish,

and that this proves helpful to you.

Posted

FYI you can have a global/unindexed field on the parent/left side of the relationship. It's fairly common to store a list of IDs in a global text field, return-separated, e.g.:

123

456

789

Any records in the child table with one of those IDs could then be used in a portal or calculations.

Posted

Hi Mleering,

Thanks for your answer, let me explain the main idea.

Image that you have multiple patients records, they can go to your clinic every month or just that time, we don’t know.

The manager can save time, and create a criteria (by default the personal only can access records modified 01-01-2011 to 31-12-2011), for example.

I can manage this dates, into a global field. OK, no problem. Then do a script to perform a found set related with these criteria.

I think it isn’t necessary to have this values (criteria) into the records tablet, because this value is global (most of time), and with global data, I can save space and make a more efficiently database.

Now the problem, I need to make statistics. The idea is the same, we do statistics time to time, so we should just use one tablet to do all statistics, it isn’t necessary to have multiple fields into the main record.

I can do this with a calculation, if (criteria = “xxx”; 1; 0) then do a relationship to statistics, so that when the field is equal to 1, perform a list with the related values.

This should work in theory, but doesn’t work, because I’m working with global fields, and the relationship use values not unique.

Must be a way to solve this.

Thanks,

Caps

Posted

FYI you can have a global/unindexed field on the parent/left side of the relationship. It's fairly common to store a list of IDs in a global text field, return-separated, e.g.:

123

456

789

Any records in the child table with one of those IDs could then be used in a portal or calculations.

Hi, Fitch thanks for your answer.

I didn’t understand your point. I’m new to filemaker, but I’m spending 3 hours by day, learning, searching, etc.. I suppose, must be a way to solve this, but I couldn’t found out.

In your example, you use unique IDs but I need to use a just one ID for relationship, when the ID is equal to the criteria, then do a list with the found set records (criteria).

As I told before, in theory this is simple, but doesn’t work in the file, because I have global fields and calculation, and this two together aren’t good for index or the index doesn’t work as due.

Thanks

Posted

I am not sure why you need another table "just for statistics". But if you want your relationship to "see" only certain records, the criteria must be placed on the "parent" side of the relationship. In your example, that would mean something like:

test4a.zip

Posted

I am not sure why you need another table "just for statistics". But if you want your relationship to "see" only certain records, the criteria must be placed on the "parent" side of the relationship. In your example, that would mean something like:

Hi Comment,

Thanks for your answer.

Why I use one tablet to do statistics?

I think is the best options, because I don’t need to have multiple fields into the main tablet. If you do statistics time to time, what’s the point of having fields like (title of chart, legends, color of background, …) in each record? If you have 9000 client records, why should be empty fields into these records?

I think the best should be, having one tablet to do all statistics, using list and relationships.

Related with the rest of your answer, you solve it in a clever way. But didn’t use all criteria, but I get the point using the parent side of the relationship. But if you have multiple tablets and you want do use one single tablet to do statistics related will all tablets, you will need to duplicate the relationship graph over and over, with a different parent relationships.

It wouldn’t be easier to use sadpc00::x_select field to do this relationship?

This field have all criteria: Get ( LayoutName )& If(z_data_mod ≥ config::gStartDate and z_data_mod ≤ config::gEndDate;1;0)*If(config::t1_visual_pc00 = "NO";1;If(admitted="YES";1;0))

We just need to relate this to statistics. I’m wonder why didn’t you use this field to do relationship, what’s the problem of this field? And how use it?

Thanks for your time,

Posted

If you do statistics time to time, what’s the point of having fields like (title of chart, legends, color of background, …) in each record?

If these choices affect all records, then they can be in global fields. And a global field can be in any table - no relationship is required to get the value of a global field.

However, doing the actual statistics - i.e. finding records, sorting them and summarizing them - is best done in the same table where the data lives.

It wouldn’t be easier to use sadpc00::x_select field to do this relationship?

No, it wouldn't - because it's not possible. The matchfield on the "child" side of a relationship must be indexed, and an unstored calculation cannot be indexed.

Posted

If these choices affect all records, then they can be in global fields. And a global field can be in any table - no relationship is required to get the value of a global field.

But this global field can be in other tablet, like a tablet call config because i don't need this values over and over (repeat in all records).

No, it wouldn't - because it's not possible. The matchfield on the "child" side of a relationship must be indexed, and an unstored calculation cannot be indexed.

Comment, but why i can't use either a trigger on modify to set a new field with the last value and make the relationship with this new field. This new field isn't a calculation, is a text field.

Thanks

Posted

Comment, but why i can't use either a trigger on modify to set a new field with the last value and make the relationship with this new field. This new field isn't a calculation, is a text field.

Technically, you could. But then you would have to modify 9000 records every time you change the dates to view. And the modification date on those records would also change. And if another user wanted to view another set of records, they would ruin it for everyone else.

i don't need this values over and over (repeat in all records).

A global field does NOT "repeat in all records". There is only one global value (per user) for all the records in the table.

Posted

Comment thanks for all.

Your personality is equal to number of the post (18.013) amazing.

A global field does NOT "repeat in all records". There is only one global value (per user) for all the records in the table.

I think i understood, with global fields we can see this value in each record, but filemaker just make one field in the main database. So we aren't creating redundant data or increasing the size or harm the performance of database with the inclusion of global fields into the main tables. I just have one value, not 9000 values (1 for each record)?

My thoughts: should be better to have just one tablet for all global fields, because I was saving database space and increasing performance. And this fields don't need relationship (this should be the main reason, to use this values in separate tablets not into the main). But I was wrong, correct?

When we see global fields in list view or table view, we aren't see a copy of global field (9000 fields) but an unique value (1 field).

Posted

My thoughts: should be better to have just one tablet for all global fields, because I was saving database space and increasing performance. And this fields don't need relationship (this should be the main reason, to use this values in separate tablets not into the main). But I was wrong, correct?

No, not necessarily. As I said, you can place global fields wherever you find most convenient. A table of global settings certainly makes sense for some situations.

However, when you want to use a global for a relationship, you must either place it in the "parent" table - or add a calculation field that gets the global's value (as I did in my file).

Also keep in mind that in a shared solution global fields revert to their "original" value at the end of a session ("original" being the value entered into the field when the file was not hosted).

This topic is 5003 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.