Relationship - index - calculated field

[StPeter]

Hi,

 

Simplified: i try to link 2 tables based on 3 fields, one of which is an un-storable calculated field (in the right side of the relationship).

 

When i visualize this relationship in a portal (of Table2) on a layout based on Table1, i get nothing.

 

Trial and error revealed that Field3 (the calculated field in Table2) causes the problem.

 

I read somewhere that in a relationship, the right-part of the relation needs to be indexed (or indexable).

FM however doesn't allow me to store the calculated field in Table2 as it uses references to other TOs in its calculation. (is indexable = storable?).

 

I think i'm conceptionally doing something wrong or there is something fundamental that i don't grasp as i simply cannot imagine that FM cannot handle this.

 

Can somebody help please?

 

Thanks a lot in advance,

Piet

 

[mr_vodka]

Yes the child side has to be indexed. What are the fields you are using to for your relationship. Also why is the one field referencing another table. Can you do it from the parent side?

[StPeter]

Thanks for your reply John,

 

"Fields in the relationship":

(see attached picture)

 

Table1="FC"    =table with only 1 record, and only global fields (=reporting selection layer)

Table2="FC_pipe"    =table with weekly extracts from our CRM ("Extr_version" is the field that represents the date of extract)

Table2="Pipe compweek"    =2nd TO of Table2, only to lookup values in the dataset of the previous week's extract

 

 

"Field referencing another table/from parent side?": to my knowledge not, i'm afraid i have to bore you with detail here:

 

Purpose is to generate an on-screen list of sales-opportunities that needs to be reviewed sequentially with each of our sales-respresentatives (for Forecast purposes).

The list of deals is a subset of all the opportunities we have in our CRM, and that are extracted every week (weekly reviews with the reps).

 

The opportunities that need reviewing (and therefore part of the list) are:

- the open opportunities expected to be contracted this quarter (filter on opportunity-status (open, not lost, ...) and expected close date)

- the opportunities that are marked as lost/abandonned SINCE LAST REVIEW (!) (as we want to question our reps as well on the opportunities that have 'disappeared') (for this i need the reference to the 'other table')

 

To simplify this selection of opportunities, i created a calculated field in Table2 (called "FC_view") that shows "Y" as a result if the above filters apply. (this calculation only works in the context of TO "FC_pipe")

 

From Table1, which is the 'report overlay', where i can select the version i want to look at (and the reference version i want to compare to), i (want to) filter the dataset in Table2, based on a global field (called "gY") which basically only holds the value "Y".

 

Pardon me if this is overload ... your help is much appreciated!

 

rgds,

Piet

 

relation.tiff.zip

[mr_vodka]

Why not have another TO of pipe from the FC TO? You can create a calc of List ( FC_pipe::OpportunityID ) to get a list of IDs from the child and then use another global ont eh parent side for Extr_version whatever that is.

[StPeter]

Thank you John, but i still don't see how this can solve the issue ...

 

If, like you suggest (at least so i understood it), i'd link the 2nd TO of Table2 ("Pipe compweek") to Table1 ("FC") instead of to TO "FC_pipe", and then use the function 'List()' to get the selection of IDs in a global field in Table1 ("FC") ... what do i select then in the relationship between "FC" and "Pipe compweek"?

 

It would basically mean that i translate the formula of the calculation-field (that is currently causing the trouble for the relationship) into the relation ("FC" > "Pipe compweek") ... which would be a very complex combination of sets of "AND"s and "OR"s (see calculation hereafter), which i doubt is feasible ... or am i missing something?

 

Selection of opportunitities in calculation field:

 

If(Extr_version=FC::gRep_Extr and Close Quarter=FC::gRep_Qtr and Revenue Line Status="Open";"Y";
If(Pipe CompWeek::Revenue Line Status="Open" and Revenue Line Status="Lost";"Y";
If(Pipe CompWeek::Close Quarter=FC::gRep_Qtr and Close Quarter>Pipe CompWeek::Close Quarter;"Y";
If(Revenue Line Status="Open";"Y";

"N"))))

 

 

Thank you for your patience,

Piet

[mr_vodka]

Can you please post a copy of your file? Your calculation seems like it can be simplified but I would like to take a look at your file first to see the other calcs as well as what you are trying to do ( which it seems as thought I am still muddy on )

[StPeter]

Hi John,

Kindly find a clone attached (sorry, i'm not allowed to hand out the data ... so i don't know if this will serve you much).

I only got started though, don't expect a lot.

rgds,

Piet

 

EMEA Storage BI v2.0 Clone.fmp12.zip

[Lee Smith]

Hi Piet, and welcome to the forums.

 

Hi John,

Kindly find a clone attached (sorry, i'm not allowed to hand out the data ..

Perfect. We shouldn't need your data, just be sure we can understand what is happening (not happening). There are a couple of ways to attach a file. One is a clone with no records, another is to remove any confidential information, and a third is to make a few false records.

 

A few bogus records can eliminate a lot of time on our part.

 

If you need a data to import for this purpose, you can find at Brian Dunning's site Sample Data

 

HTH

 

Lee

[StPeter]

Thanks Lee, very useful link (BTW: really good stuff you guys are doing here, impressive! congrats, and keep up the good work)

 

@ John:

can it be that i found the solution myself? ... i changed the type of this 'calculation field' to a normal text field, but with 'auto-enter calculation & replaces' (using the exact same formula) ... in this way FM indexes this field and the relation can be established ... and it seems to work, tada!

 

... or did i underestimate this one and created herewith some other evil?

 

thanks guys,

Piet

[mr_vodka]

Personally, I had wished you had left some data in the file and that you could have then explained what you expected to happen. I dont even know what these fields are supposed to do and I dont think you need many of them.

 

Your calc doesnt make much sense to me because it is using a circular relationship. The calc references the parent FC TO and at the same time the calc is used in the parent / child relationship.  To me from your previous posts, it seems as though you want to put in a week and show the "open" records as well as any previous ones that may still need to be looked at again. The status is determined by the Line Status field? Sorry but I am having a hard time following the logic here.

[StPeter]

Ok John, here you go ...

 

I took a new Clone-version (after i changed the field-type like described above) and added some fake data so to picture different possibilities - see attached).

 

Purpose:

- Have a list of sales-opportunities that need to be reviewed with the reps.

- Focus is limited to those opportunities that will/can be closed during the current quarter (FY13Q4)

- in order to monitor also those opportunities that were recently lost, also these need to appear in the list (=last week still 'open' opportunities ("Revenue line status"), now 'lost')

 

Tables:

- pipeline: holds all the opportunity information - 1 record per opportunity - table is fed by weekly extracts from our CRM, an identifier of each extract is put in the field "Extr_version" and is basically a reversed and text-ed date (this actually means that each opportunity will appear multiple times in the table, with a different Extract-identifier)

- reporting info: hold only 1 record with globals - sets that extracts that need to be compared and the relevant quarter

 

For this purpose:

- look at layout "FC" please, based on TO "FC" (=reporting info), with portal based on TO "FC_pipe" (=pipeline data current week) and TO "Pipe compweek" (=pipeline data comparison week)

- from the 6 opportunities in the dbase it now shows 5 (as desired, or actually, i should still remove the 'won' opp but that's a matter of changing the calculation)

 

I hope this is more understandable,

rgds,

Piet

 

EMEA Storage BI v2.0 Clone.fmp12 2.zip

[mr_vodka]

Ok. What records do you want to show in the portal? From what I gather it seems as though you want to show records with an open status for a specific extract number. You also want to show that current list with some data columns from a specified previous extract number that you would like to specify ( which usually is the previous week ) ? Am I correct to assume this? I still dont understand what the multiple IFs are in your FC_view calc ( which BTW can be simplified using a Case statement ). I dont understand the purpose of this calc and it seems that the multiple checks arent even needed.

[StPeter]

1. "What records do you want to show in the portal?"

 

- all opportunities, from the current extract, that have a foreseen closing date in this quarter, and that have a status of 'open' (=not won, nor lost)

hence the formula: If(Extr_version=FC::gRep_Extr and Close Quarter=FC::gRep_Qtr and Revenue Line Status="Open";"Y";

 

OR

 

- all opportunities, that were still in an 'open' status last week (=last week's extract), but which have been closed from some reason in the meanwhile (i want to review these with the sales reps as well as i want to know what happened)

hence the formula: If(Pipe CompWeek::Revenue Line Status="Open" and Revenue Line Status="Lost";"Y";

 

OR

 

- all opportunities, that were relevant for this quarter last week (=in last week's extract), but that have moved to the next quarter for some reason (also these i want to review with our sales reps)

hence the formula: If(Pipe CompWeek::Close Quarter=FC::gRep_Qtr and Close Quarter>Pipe CompWeek::Close Quarter;"Y";

 

 

(the last part of the formula (="If(Revenue Line Status="Open";"Y";") can be omitted.

 

 

2. I don't want to show comparison data between this week and last week per se, i included this in the file that i sent to explain the selection of the records from your previous post.

 

Hope this helps,

rgds,

Piet