The database has table1 with “Sample No” field relating it to table2 with “Sample No” field. Table 2 has a portal on a layout with Table1. Each portal row has the Category field with one of 3 values (MF,BP and CC). There are about 7000 samples. Each sample has from 1-12 portal rows displayed with 1 or more occurrences of the 3 category values one of the values shows the count of each value per sample.

 

The question is how do I write a script so that every sample has all 3 category values (MF,BP and CC)?

 

I have the following scripts for when there is only one value/portal - one for each of the 3 values (3 scripts are the same except that “Molecular Function” is substituted for BP or CC and “Molecular Function” = MF):

 

Perform Find [ Specified Find Requests: Find Records; Criteria: Transcript Database::Regulation: “=test” ] 

 

[ Restore ] 

Perform Script [ “Sort by Sample No” ] Go to Record/Request/Page 

[ First ] 

Loop
Go to Portal Row 

[ Select; First ]
If [ GO_GOT::Putative Gene Function Category 

"Molecular Function" ] 

≠ "Molecular Function" ] 

≠ If [ GO_GOT::Putative Gene Function Category 

Go to Portal Row 

[ Select; Next ] 

Go to Portal Row 

[ Select; Last ]
Set Field [ GO_GOT::Putative Gene Function Category; "Molecular Function" ] 

End If End If 

Go to Record/Request/Page 

[ Next; Exit after last ] 

End Loop 

 

I can not get a script to work on a portal with only 2 categories so that only one of the 3 categories is added per sample.

Transcript Database v05 test.fmp12

For clarity, I will be using the names Parent and Child for the two tables.

5 hours ago, Volker Gurtler said:

how do I write a script so that every sample has all 3 category values (MF,BP and CC)?

I would suggest you start by identifying which values are missing. This can be done by setting a variable to the following calculation =

Let (
existingValues = List ( Child::Category )
;
List (
If ( IsEmpty ( FilterValues ( "MF" ; existingValues ) ) ; "MF" ) ;
If ( IsEmpty ( FilterValues ( "BP" ; existingValues ) ) ; "BP" ) ;
If ( IsEmpty ( FilterValues ( "CC" ; existingValues ) ) ; "CC" ) 
)
)

Once you have that, you can go to a layout of the Child table and create a record for each missing value. Here's a complete script for one Parent record:

Set Variable [ $missingValues; Value:Let ( 
existingValues = List ( Child::Category ) 
; 
List ( 
If ( IsEmpty ( FilterValues ( "MF" ; existingValues ) ) ; "MF" ) ; 
If ( IsEmpty ( FilterValues ( "BP" ; existingValues ) ) ; "BP" ) ; 
If ( IsEmpty ( FilterValues ( "CC" ; existingValues ) ) ; "CC" ) 
) 
) ] 
If [ not IsEmpty ( $missingValues ) ] 
  Set Variable [ $parentID; Value:Parent::ParentID ] 
  Go to Layout [ “Child” (Child) ] 
  Loop 
    Set Variable [ $i; Value:$i + 1 ] 
    Exit Loop If [ $i > ValueCount ( $missingValues ) ] 
    New Record/Request 
    Set Field [ Child::ParentID; $parentID ] 
    Set Field [ Child::Category; GetValue ( $missingValues ; $i ) ] 
  End Loop 
  Go to Layout [ original layout ] 
End If 

 

---

Note:

There is something fundamentally "wrong" with this requirement. Your records should reflect some physical reality. If the parent does not have children in some category, then that should be the end of it. There should be no need to artificially manufacture a fake record that doesn't represent a real object.

I agree with Comment's assessment. Somethings wrong here.

That said, this would be my approach, see attached PDF.

TranscriptDB.pdf

I am very grateful to you for your solution and as soon as I have tried it out successfully I will let you know. But firstly I hope the information below will answer your question.

 

To make sense of my requirements, the following information may help your understanding.

 

Database exported as excel spreadsheet:

 

Description                            Sample No           Category        Count of Category    

negative regulation of peptidase activity        5        Biological Processes    1

 peptidase inhibitor activity                    5        Molecular Function        1        

 negative regulation of endopeptidase activity    6        Biological Processes    1    

 extracellular space                            6        Cellular Component        1        

endopeptidase inhibitor activity                6        Molecular Function        1        

 myosin complex                            8        Cellular Component        1    

 motor activity                                8        Molecular Function        2        

zinc ion binding                            14        Molecular Function        1    

 cytoskeleton organization                    15        Biological Processes    1

 

But this needs to be transposed to the final desired table:

 

                    5    6    8    14    15

Biological Processes    1    1    1        1

Cellular Component            1    2        1

Molecular Function        1    1        1    

 

But for this table there are the following requirements:

1. Each sample no needs to have 3 records each with one of the category values (Molecular Function;Biological Processes; Cellular Component)
2. One of the layouts has a filter that removes duplicates in the category field so that only a max of 3 rows are displayed
3. If there are more than 3 records per sample or more than one record with the same category value then the count of category value will be greater than 1.
4. For those records for which an extra field has been created using the requested script, the count is “0” but for the creation of the above table it is left blank for analysis using a log scale (0 turns to 1 making graphs difficult to interpret)
5. It is important to have these blank values otherwise the final analysis cannot be made.

5 hours ago, BruceR said:

I agree with Comment's assessment. Somethings wrong here.

That said, this would be my approach, see attached PDF.

TranscriptDB.pdf

I am very grateful for your both solution and as soon as I have tried it out successfully I will let you know. But firstly I hope the information below will answer your question.

 

To make sense of my requirements, the following information may help your understanding.

 

Database exported as excel spreadsheet:

 

Description                            Sample No           Category        Count of Category    

negative regulation of peptidase activity        5        Biological Processes    1

 peptidase inhibitor activity                    5        Molecular Function        1        

 negative regulation of endopeptidase activity    6        Biological Processes    1    

 extracellular space                            6        Cellular Component        1        

endopeptidase inhibitor activity                6        Molecular Function        1        

 myosin complex                            8        Cellular Component        1    

 motor activity                                8        Molecular Function        2        

zinc ion binding                            14        Molecular Function        1    

 cytoskeleton organization                    15        Biological Processes    1

 

But this needs to be transposed to the final desired table:

 

                    5    6    8    14    15

Biological Processes    1    1    1        1

Cellular Component            1    2        1

Molecular Function        1    1        1    

 

But for this table there are the following requirements:

1. Each sample no needs to have 3 records each with one of the category values (Molecular Function;Biological Processes; Cellular Component)
2. One of the layouts has a filter that removes duplicates in the category field so that only a max of 3 rows are displayed
3. If there are more than 3 records per sample or more than one record with the same category value then the count of category value will be greater than 1.
4. For those records for which an extra field has been created using the requested script, the count is “0” but for the creation of the above table it is left blank for analysis using a log scale (0 turns to 1 making graphs difficult to interpret)
5. It is important to have these blank values otherwise the final analysis cannot be made.

I am afraid this is quite unreadable. It seems that your real purpose is to export the data in the format of a pivot table. But you did not ask about that. Instead, you have created an XY question..

 

I am very sorry not to have included this in the original question but you were correct - the pivot table was the answer. With some moving of rows and columns the correct table was obtained. Thank you very much - I have now learnt a great deal about pivot tables and scripts!