Indirect Selection (P function etc.)

[#1]

Hi

I've two data elements in two different tables

Table_1.contact_id (this is one of the data elements in Table 1)

Table_2.contact_id (this is one of the data elements in Table 2)

The data model is structured in a way that these two tables are not linked using contact_id because of circular reference issues in the larger scheme of data model.

However, for certain computations i need to assign Table_2.contact_id based on the selection of Table_1.contact id.

i.e. The user on the front end is going to select Table_1.contact id, However, when that selection is made i need to make that same selection on Table_2.contact_id

I've tried using p function to do this. For example, writing the code Table_2.contact_id = p(Table_1.contact_id)

For some reason this didn't work.

Is there any other way to make this indirect selection?

Thanks

[vgutkovsky]

Kind of a loaded question--basically, it depends on how your data model is structured. I'm guessing, from your description, that Table1 and Table2 are connected somehow (i.e. that neither one is an island). If so, keep in mind that selecting a set of X values of Table1's contact_id values will have an associative impact on Table2's possible universe of contact_id values. So you may need to also ignore Table1's contact_id field in the same set analysis: sum({<Table_1.contact_id,Table_2.contact_id=P(Table_1.contact_id)>} Sales)

But really this is just a guess. If you need more help, posting screenshots of your data model and expression might help.

Regards,

Vlad

[swuehl]

Could you detail what you mean with 'it doesn't work'?

Most of the time when you get issues with p() function, you need to clear other field selections to avoid an incompatible set, just like Vlad mentioned.

You also need to assure that there are possible matches in the field value.

If you create two list boxes for the contact_id fields of both tables, then make your selections in table1 and as well all the selections you may imply in your set analysis, are there matching value possible in the contact_id of table2 list box?

Just as a side note, if you want to assign the set of selected table1 values, you could also write

{<Table2.contact_id = Table1.contact_id >}

i.e. no need for the p() function in that case ( the p() function will also assign a set when there is no selection made in table1 contact_id).

[#4]

Hi Vlad

Yes, the two tables are indirectly connected and they are not islands by themselves.

I modified the code per your suggestion below, however it still doesn't seem to be working.

Sum({ 1< parent_clone_type = {'Parent'},

               rating_contact_id,

               history_rating_contact_id = p(rating_contact_id),

               security_id_effective_date2 = p(security_id_effective_date)

        >}

benchmark_contrib_mktval_weight)

Following is a pic of part of the data model, you'll see core_rating and core_security_history are the two tables i am referring to core_rating has rating_contact_id which the user selects on the front end, however i need to get history_rating_contact_id selected in order to get the correct benchmark_contrib_mktval_weight sum. I've writing following two lines of codes in order to achieve it but it is still not equating history_rating_contact_id TO rating_contact_id

rating_contact_id,

history_rating_contact_id = p(rating_contact_id),

Thanks

[swuehl]

Have you tried to select the possible values manually in list boxes?

You are also modifying this date field:

  security_id_effective_date2 = p(security_id_effective_date)

where you can have same issues, i.e. you may need to clear security_id_effective_date field to avoid incompatible sets.

[vgutkovsky]

Yep, add security_id_effective_date to be ignored in your set.

[swuehl]

Just noticed that you are using set identifier 1 in your set analysis, so I don't think it's a matter of clearing other fields, so your set expression can be limited to:

Sum({ 1< parent_clone_type = {'Parent'},

               history_rating_contact_id = p(rating_contact_id),

               security_id_effective_date2 = p(security_id_effective_date)

        >}

benchmark_contrib_mktval_weight)

Can you detail what your expected results are and what you see instead? If you get zero returned even when no selections are made. it seems to me that the combination of the field modifications are incompatible, resulting in an empty record set for the aggregation scope. I would specifically look into the impact of 'Parent' set to parent_clone_type to the possible values of history_rating_contact_id and security_id_effective_date2. There still need to be some common values with rating_contact_id resp. security_id_effective_date.

Without knowing your data model (and the possible field values), as well as why you think the expression is not working, it's hard to tell what my be wrong.

[#8]

Hi Vlad

added security_id_effective_date to be ignored in set analysis but it still didn't work.

To explain the problem a bit differently. The user is making following two selection on the front end. (Effective Date and rating_contact_id)

However, using the set analysis i need to make selection on history_rating_contact_id.

Used following set analysis but it doesn't seen to be working.

Sum({ 1< parent_clone_type = {'Parent'},

               rating_contact_id,

               history_rating_contact_id = {'917'},//p(rating_contact_id),

               security_id_effective_date,

               security_id_effective_date2 = p(security_id_effective_date)

        >}

benchmark_contrib_mktval_weight)

Thanks

[vgutkovsky]

Ah, that's helpful. Can you do one more screenshot with the following: (1) add list boxes for security_id_effective_date2 and benchmark_contrib_mktval_weight, (2) in addition to what you have selected above, physically click (a) Parent in parent_clone_type, (b) 917 in history_rating_contact_id, and (c) 2016-01-29 in security_id_effective_date2

Thanks,

Vlad

[#10]

Hi Vlad

Please note that security_id_effective_date2 is actually concatenation between a security id and effective date so i've selected one of the security's effective date here i.e. 203_2016-01-29. 203 being one of the securities.