filter multiple IDs in single portal

[mjhnson24]

I have a table Customer Scripts. There can be multiple script records for each Client ID. Lets say theres 7 records for Client ID 6070. each script record has multiple paragraph records displayed in a portal related to customer scripts through a join table via paragraph ID and CustomerScript ID.

 

So any one of the 7 customer script records could have the same paragraph in them just related to that customer script ID also.

 

I have a layout that is relating to that join table via client ID (I capture the client ID and add it to the join table record as each paragraph is added to the customer script record just in case i need it), so the each of the 7 scripts could be using Paragraph ID SP4612 so the portal would be showing 7 records in it just for that paragraph id since its related via client id.

 

How do i filter that portal to only show one copy of that SP4612 paragraph ID? I only need one to show in the list so they can choose it if needed to add to a new script record. Keep in mind that i do have a current filter on that portal that allows me to type into a field and filter the portal even further with the records being shown so i can Type SP4612 in that field and only the record for SP4612 will show. That filter script is below.

[mjhnson24]

Case (

 

 

/* Global Filterfield. */

Let ([ filterField = Customer Scripts::_PORTAL_FilterPreviousParagraphs ];

 

 

/* These are the fields the filterfield searches across. */

(

PatternCount ( CustomerScripts|ScriptParagraphsJoin__ClientID_CustomerScriptsClientID::ScriptsParagraphID ; filterField ) > 0 or

PatternCount ( CustomerScripts|ScriptParagraphsJoin|ScriptParagraphs::ScriptWriter ; filterField ) > 0 or

PatternCount ( CustomerScripts|ScriptParagraphsJoin|ScriptParagraphs::Type ; filterField ) > 0 or

PatternCount ( CustomerScripts|ScriptParagraphsJoin|ScriptParagraphs::Paragraph ; filterField ) > 0 or

PatternCount ( CustomerScripts|ScriptParagraphsJoin|ScriptParagraphs|PrimaryTopic::ParagraphTopicName ; filterField ) > 0 or

PatternCount ( CustomerScripts|ScriptParagraphsJoin|ScriptParagraphs|SubTopic::SubParagraphTopicName ; filterField ) > 0 or

PatternCount ( CustomerScripts|ScriptParagraphsJoin|ScriptParagraphs::KeyWords ; filterField ) > 0 )

and not IsEmpty ( filterField ) or

IsEmpty ( filterField )

) ;

 

 

/* This is for refreshing the portal count. Portals with filters can only use this type of count function. */

Let ([

$$PortalCountPara1 = Case ( IsEmpty ( $PortalCountPara1Indicator ) ; 0 ; $$PortalCountPara1 ) + 1 ;

$PortalCountPara1Indicator = True

 

 

] ;

 

 

True

 

 

))

[mjhnson24]

So does anyone know how to do this?

[doughemi]

Please explain what you are trying to accomplish, not how you are trying to accomplish it.

 

What is the desired end result?

Are the paragraphs just boilerplate copy which may or may not apply to a script?

What is the purpose of the script as related to a customer?

[mjhnson24]

Its a join table. Holds records for paragraphs for each script. Its related to the script table via CustomerScript ID and ScriptPAragraphID. But in the process of adding a paragraph to the table i add the client Id of the current paragraph. Each Client can have more than one record for customer scripts and each customer script can have more than one paragraph including some of the same paragraphs as a previous script (hence why I'm using a join table).

 

 

If i related that table on a layout via client ID, if I'm on client 6070, it will show paragraphs in the join table that are associated to that client ID even though they may be associated with a different customer script ID.

 

SP4612

SP5119

SP0929

SP0930

SP0931

SP0932

SP0933

SP4685

SP0933

SP0931

SP0932

SP0929

SP4612

 

But i want it to just narrow down to not show duplicate IDs. If the ID is in the portal more than once it only needs to show once. Below is what it needs to be.

 

SP4612

SP5119

SP0929

SP0930

SP0931

SP0932

SP0933

SP4685

 

Heres my reasoning. My boss wants to have the ability to show all paragraphs previously used on previous scripts for that current Client ID being viewed so if they want a previous paragraph they can use it or choose a new one and so on.

[doughemi]

How about a value list RelatedParagraphs, using values from the field ScriptParagraphs::ParagraphID including only related records starting from Customers, and a calculated field in Customers = ValueListItems(RelatedParagraphs)?

[mjhnson24]

I thought of some kind of calculated field but is it gonna be slow rendering when it goes to that layout since its a calculated field?

[doughemi]

Not unless you have, like, 200,000 paragraph records. It shouldn't be slower than drawing the portals.

[mjhnson24]

Oh ok cool. i know i got about 67396 records in that table all together but not all for one client though.

[mjhnson24]

Well i did a script that got a list of all the related paragraph IDs for that particular client ID and set a field with that data and it was related via the client ID and the return delimited list but because the join table has more than one record in it for that paragraph ID for different clients i have to relate it via the client ID as well but regardless it still pulled up more than one record of the same ID because there is more than one record of the same paragraph related to that client ID even though it may be to an older customer script record.

 

Customerscript ID: 78108

Client ID: 6070

ScriptParagraphID: SP4612

 

Customerscript ID: 90018

Client ID: 6070

ScriptParagraphID: SP4612

 

Customerscript ID: 90018

Client ID: 6070

ScriptParagraphID: SP4865

 

Even though its two different CustomerScriptIDs its got the same client ID so it looks like

 

SP4612

SP4612

SP4865

 

When i need it to just be

 

SP4612

SP4865

 

No need to have it list the same ID twice.

[doughemi]

There is no reason to use a script. See attached demo.

ScriptParas.fp7.zip

[mjhnson24]

Ok heres an example of what i have right now. As you can see if your on the Customer Scripts Layout, the top portal is just paragraphs related to that current CustomerScriptID being viewed but the bottom portal is related to the client ID of the script being viewed. Each client has 3 Customer Scripts records. The bottom portal shows multiple records of the same paragraph just because the other 2 scripts related to that client id has the same paragraphs even though they are old customer script records and each record is obviously a single record. this is what i need but i need the bottom portal to filter out duplicate Script Paragraph IDs ( But not delete them cause they are needed ).

 

There is a reason for that and its because i have a customer script editor that allows them to see any past paragraphs that was used on previous scripts that they can choose form when adding a new customer script record for that client and if they se one they can click to grab that ScriptParagraphID and add a new record in the ScriptsParagraphJoin table to the Current Customer Script Record with the Customer scriptID, client ID and the ScriptParagraph ID they chose from a previous customer script.

test.fp7.zip

[doughemi]

Does this help?

test Mod.fp7.zip

[mjhnson24]

Actually thats exactly what im looking for. Thanks alot for your help. Now i just need to implement it into my real solution instead of that test solution i posted here. Again thanks alot.

[baskoro]

its great..but i need help.

i have record

number week proses

001 1 20%

001 2 100%

002 1 10%

002 2 90%

003 1 50%

 

i want see in portal:

number week proses

001 2 100%

002 2 90%

003 1 50%

show the last week for each number in portal, it's possible?

thanks,,