1 2 Previous Next 22 Replies Latest reply on Jan 18, 2012 8:13 AM by philmodjunk

    Numbered list of multiple records to one field

    matus.cechvala@gmail.com

      Title

      Numbered list of multiple records to one field

      Post

      Hello,
      I need to join multiple records and fields to one field. Can anybody help me with this task?

      I have this content:

      MainPos SubPos Publish Name Price OUT
      1 0  Y  Aaaaaaaa  25,00  
      1 1  Y  Bbbbbbb  15,00  
      1 2  N  Cccccccc  10,00  
      1 3  Y  Dddddddd  5,00  
      2 0  Y  Eeeeee  10,00  
      2 1  N  Fffffff  15,00  
      2 2  Y  Gggggggg 5,00   


      And I need to have in records with SubPos = 0 in field “OUT” the numbered list of Name & Price, sorted by Price, if "Publish" value is Y:

      For record 1|0 it means OUT field contains:
      1. Dddddddd 5,00
      2. Bbbbbbb 15,00
      3. Aaaaaaaa 25,00

      For record 2|0 it means OUT field contains:
      1. Gggggggg 5,00
      2. Eeeeee10,00

      (other OUT fields are not important, they could stay empty)

        • 1. Re: Numbered list of multiple records to one field
          philmodjunk

          How will you use this field?

          You appear to show them listed sorted by Price in increasing order. Is this important?

          A self join relaitonship and the list function might be able to do this, but I need to nail down the details and this could be something that can be accomplished without defining such a calculation field.

          • 2. Re: Numbered list of multiple records to one field
            matus.cechvala@gmail.com

            Source records are not sorted by Price, I get the values from client. But in output field I need them in increasing order.

            The order of all database is not important, I can reorder it by MainPosition and Price if necessary.

            In output I'll work only with records with SubPos = 0, that's why I need to join all valuest into one field.

            • 3. Re: Numbered list of multiple records to one field
              philmodjunk

              In output I'll work only with records with SubPos = 0, that's why I need to join all valuest into one field.

              Please explain that in more detail. This can be done in many ways that do not require such a calcualtion field.

              • 4. Re: Numbered list of multiple records to one field
                matus.cechvala@gmail.com

                The input data comes from the custormer's business system: I get product numbers (unique IDs), product names grouped using the position and subposition value and the product price. There are hunderds of products (records). The names are not always correct, we have database of correct names, we replace source names with the correct one from our database. This part I have ready. 

                And now we need to return selected data (with Yes in Publish field) back to other system. It expects soething like "Group name" (it's in database too, it's joined with SubPos = 0 record). An then there is one filed with all values described in firs post expected. 

                The problem is the input system and the target systems are not joined. Target system is again on the customer side, we can not modify it to any clever input data structure. I use FrameMaker like some tool to connect 2 incompatibile systems.

                • 5. Re: Numbered list of multiple records to one field
                  philmodjunk

                  Ok, I had to test this one out in a demo file to make sure that it worked. I wasn't sure I could get the list to be numbered correctly or not, but tests reveal that the following method works:

                  Define a calculation field, cListItem as: Get (RecordNumber )& " " &  Name & " " & Price

                  Specify that the calculation field be Unstored by clicking the storage options button.

                  Define a second calculation field, constYes and specify "Y" as the sole term in its calculation. Both of these fields should be set to return Text.

                  Go to Manage | Database | Relationships, click your table to select it and use the duplicate button (Two green plus signs) to make a new occurrence of the same table. Link the two occurrences in this relationship:

                  OriginalTable::MainPos = OriginalTable 2::MainPost AND
                  OriginalTable::constYes = Originaltable 2::Publish

                  Specify that the records in Originaltable 2 be sorted by Price in ascending order.

                  Now define Out as a calculation field:

                  If ( MainPos ; List ( OriginalTable 2::cListItem ) )

                  and it will return the values as you have specified them.

                  • 7. Re: Numbered list of multiple records to one field
                    philmodjunk

                    Good one! I especially like the extra code used to format the prices correctly.

                    Using a new file share site? (I'm not real thrilled with 4shared these days myself...)

                    • 8. Re: Numbered list of multiple records to one field
                      matus.cechvala@gmail.com

                      Hello LaRetta, thank you for your example. 

                      Your code works great, there is only one issue - the numbering is not  continous. If I added some new reccords, there are gaps - I see 1,2,3,6,7. I can't fing why, I use your sample file.

                      And 2nd question - is it' necessary to be Position indexed? :(
                      My example was a little bit simplified. In reality I get the position and subposition in one field* and so I use 2 calculation fields to separate it.

                      If I try to set the "Position" field as Indexed, I can't. I have to calculate this field value and I can't switch Indexing on (I can't switch off the Option "Do not store calculation results". I don't see  where is the error, other calculated fields I can set as Indexed, but not this one. It's not the reference field, it's just easy calculation of 2 field values (PageNumber and MainPosition on Page to have unique position number).

                      *(not very clever as I noticed. They send me the position and subposition as decimal number, what is pretty stupid. Value 2.1 is in this case something different then value 2.10. I have to split this source field into 2 calculated fields. 

                       

                      Price formating is realy genial! :) (especialy if I compare to my curent solution :( )

                      • 9. Re: Numbered list of multiple records to one field
                        philmodjunk

                        Your code works great, there is only one issue - the numbering is not  continous. If I added some new reccords, there are gaps - I see 1,2,3,6,7. I can't fing why, I use your sample file.

                        Make sure that the calculation field, (the one referred to in the list function) is set to be "unstored" so that it doesn't evaluate only once when the record is created.

                        If I try to set the "Position" field as Indexed, I can't.

                        This indicates that your calculation refers to a field in another table or a field that is unstored or global. You may need to use an auto-enter calculation set on a number field. (This should work as long as the value is not subject to change after you import the data.)

                        • 10. Re: Numbered list of multiple records to one field
                          matus.cechvala@gmail.com

                          I can't find any of reasons you write, I have created new one field named IDposition to be sure there is no reference and the field value is used nowhere - and no success.

                          There is easy calculation based on 2 fields, both are in the same Table: Page and Position.

                          There could be max. 20 positions on one page, so I have decided to use this formula: = Page + Position * 0,01 . For position 1 on page 1 I get 1,01, then follows 1,02 etc. that is OK.

                          If there is only one field and IDposition = Page or IDposition = Position * 0,01, I can switch "DO not store calculation..." off.

                          If the formula contains both values and IDposition = Page + Position * 0,01 - The switch "DO not store calculation..." is automaticaly set ON and I can't switch it off :(

                          Any idea how to solve it? I can set Indexed on Page and on Position fields too, but on the combination of both values ther is no common Index I'm affraid. And I can't create unique position number in multipage document source :(

                          • 11. Re: Numbered list of multiple records to one field
                            philmodjunk

                            Can you upload a copy of your file to a file sharing site for us to take a look at? It can be a clone (save with that option to leave out all data).

                            The only options that should "lock" you into an unstored calculation is if Page or position has global storage enabled. If they do, you can work around that by using a number field with this expression as an auto-entered calculation.

                            • 12. Re: Numbered list of multiple records to one field
                              matus.cechvala@gmail.com

                              Here is the link to files:

                              http://www.uschovna.cz/zasilka/G1HYVXBIML8YX4S8-LT5

                              Fileds in ZADANI_2-2 are Page, Pozice (=Position) and ID_pozice

                              • 13. Re: Numbered list of multiple records to one field
                                philmodjunk

                                I would assume that Pozice is the field that you originally identified as "MainPos" above and it is the one you could not change to "stored"?

                                I opened Pozice by double clicking it in Manage | Database | Fields. I then clicked the storage options button. I then cleared the "do not store..." check box by clicking it.

                                I then exited all dialogs by clicking OK.

                                The field is now stored in the copy that you sent to me.

                                • 14. Re: Numbered list of multiple records to one field
                                  LaRetta_1

                                  Well, I didn't test Phil's idea (I thought it was clever, Phil) and it appeared to work so I dropped the file I was creating.  I had originally put together a custom function to assign the numbers.  It still may be the best approach if Phil can't get you working using the other option.  I have to leave so here is what I created: 

                                  http://www.directlinesolutions.com/downloads/Source.zip

                                  BTW, I had an error in that number formatting calc and I corrected it. 

                                  I am sorry but I have no idea what you are talking about in reference to position or pages.  Can you please explain "And 2nd question - is it' necessary to be Position indexed? :(

                                  Hey Phil, I was using 4shared for a bit but I'm back loading files onto my server instead.  :-)

                                  1 2 Previous Next