8 Replies Latest reply on Jul 7, 2015 12:31 AM by Snoopy7

    Conversion from portal LineItems to one single row of fix fields

    Snoopy7

      Title

      Conversion from portal LineItems to one single row of fix fields

      Post

      Hello

      I have already a supporting table for preview of 3 portals, but need new table for Word export

      Existing supporting table PRINT_REPORT

      | Field(Art) | (Abbrev) | (Number) |      (Titel) |

      ------------------------------------------------------

      | Directive |  LVD    | 2006/95/EC            | Low Voltage |

      ..(max 1-5)

      | Regulation | (empty) | (EG) 1275/2008         | Ecodesign requirements |

      . .(max 1-5)

      | Standard   | (empty) | EN 60335-1:2012+A11:2014 | Household - Safety - Part 1: General requirements |

      ..(max 1-20)

      Aim :

      This table is for Word export and since all data need to be in one record it need to be converted to one row of fields only. New table DoC-EXPORT single row should look with fix field names like:

      REG_01 | .. | REG_5 | DIR_01 | .. | DIR _05 | STD_01| .. | STD_10|

      Not used fields stay empty. I tried a few things and googled but have no success. Can you give some tips ?

      Thank you

      Hans

       

        • 1. Re: Conversion from portal LineItems to one single row of fix fields
          philmodjunk

          Calculation fields can use GetNthRecord to access records in specific rows of unfiltered portals. Thus, you can set up a series of calculation fields that copy the contents of the different portal records in order for your export to work.

          • 2. Re: Conversion from portal LineItems to one single row of fix fields
            Snoopy7

            Hi

            Thank you GetNthRecord is the right direction. The first part REG is fine (sort in relationship works), but now I struggle with the two other self-join tables to PRINT_REPORT which should sort according custom sort for DIR / STD but don't in the relationship.

            hmmm...

            Regards

            Hans

            • 3. Re: Conversion from portal LineItems to one single row of fix fields
              philmodjunk

              If you want assistance with that, you'll have to explain it in more detail.

              • 4. Re: Conversion from portal LineItems to one single row of fix fields
                Snoopy7

                Hello Phil

                The base table PRINT_REPORT has some sorting.

                The idea with you input is to use with GetNthRecord for REG/ DIR / STD the first records from a self join table REG/ DIR / STD sorted in the self join relationship (as else according help does not work). When sorted I would use for fix fields an If = REG/ DIR / STD then GetNthRecord calculation (I dont know yet how exactly) to avoid filling up with following records from another kind. So only REG/ DIR / STD would show in their fix fields in DoC-EXPORT.

                Now sorting of STD works with a script trigger "Sort STD" at onRecordLoad / OnLAyoutEnter. Meaning the table showing the right sort, but GetNthRecord out of the table DoC-EXPORT will not follow sorted records. I have read it will use only sort records from table relationships. So in self join relationship UniqueID=UniqueID I use sorted records with customs value list to REG/ DIR / STD. Now this relationship sorting does not work. If I switch between the table layout view PRINT_REPORT or REG -> STD it will not change, hence DoC-EXPORT showing the wrong records.

                If you can assist again that would be great.

                • 5. Re: Conversion from portal LineItems to one single row of fix fields
                  philmodjunk

                  Please try that again. You have a data source table named Print_Report.  it has a self join relationship? What are the details of that? What is the name of the other TO of this table? REG/ DIR / STD is the value in a field of that table? (First read like it was a table occurrence name...) What field?

                  Then you mention "sorting of STD". What does that describe? Is that the field storing this value? or something else?

                  Sorting via a script will not change the sort order of related records. To specify a sort order for related records, you'd go to Manage | Database | Relationships and double click the relationship line to open a dialog where you can spell out a sort order for the relationship.

                  But it's not clear to me that a sort is even needed here.

                  If you want to only return data from related records with a specific value, you can set up a relationship such that records with that specific value are the only related records.

                  • 6. Re: Conversion from portal LineItems to one single row of fix fields
                    Snoopy7

                    Dear Phil

                    I tried again with GetNthRecord and sorting tables but got stuck. Nevertheless had success even more simply with SQL getting a value list for each field:

                    ExecuteSQL ( "SELECT col6 FROM PRINT_REPORT where Sort <> ? and DoC = 'CE' or  DoC = 'CO'"; ""; "";  7)

                    ExecuteSQL ( "SELECT col6 FROM PRINT_REPORT where Sort = ? "; ""; ""; 7 )

                    So this issue is solved, but I phased a new small problem with MS-Word export only few times due to the large text size now for one field. I get: runt ime error 5009. Text larger than 255 characters in following script sequence:

                    "[EditReplace.Find=\"<<STD_01>>\",.Replace=\"" & DoC_EXPORT::STD_01 & "\",.ReplaceAll,.Wrap=1]"

                    In case you know some Workaround for this I'd be happy. Else see this as closed.

                    Thank you for the help!

                     

                     

                     

                     

                    • 7. Re: Conversion from portal LineItems to one single row of fix fields
                      philmodjunk

                      Sorry, but I still don't have a clear enough picture of what you are trying to do. Please note that this is the first mention of MS Word being part of this issue as one example of the incomplete picture that I have of your database and what you want to do.

                      Did you know that you can export to a file of type .mer (merge)? And that this export was originally designed as a format that allows you to do a mail merge with MS Word? Don't know if it will do the job here for you or not given my imperfect level of understanding but am mentioning it in case you were not aware of this option.

                      • 8. Re: Conversion from portal LineItems to one single row of fix fields
                        Snoopy7

                        Phil

                        Great, I did not know .mer files so I will check on this.

                        Thanks again for your good Support!