4 Replies Latest reply on Nov 6, 2014 12:37 PM by SeijoKoibito

    Comparing Record to Record+1 in calculation field but only getting "?"

    SeijoKoibito

      Title

      Comparing Record to Record+1 in calculation field but only getting "?"

      Post

      I don't know what's going on with this, as when I preview it, ALL ¶/cr/lf formatting is stripped, and it is practically unreadable.  As it states Textarea(wysiwyg/html), I am boggling over the fact that text IS NOT formatting "like you would in a word processor."  Hopefully this is only in preview mode and not with the post, as I can find no way to change the formatting.  This concerns two related tables (out of 28) in a FileMaker Pro 10 database file. SHOWS ========== ShowCode (number) S_Type (text) S_Show_E (text) S_Show_J (text) S_Show_K (text) S_Show_X (text) S_Description (text) S_Art (container) S_OpenDate (date) S_CloseDate (date) MEDIA(SH) ========== ShowCode (number) Restricted (text) y/n Completed (text) y/n Reveal (text) y/n Used (text) y/n Ship (number) Total (number) S_Show_Pref (number) Storage1 (text) Storage2 (text) Storage3 (text) Out (calculation-text) Tables are related by ShowCode.  These two tables have a One-to-One relationship. The SHOWS table holds the names of the item being referenced (in English, Japanese, Korean and Chinese). This worked when we had a completely separate file and used a repeating field to handle up to 90 or so values in each of the Storage1, Storage2, and Storage3 fields (now those are in their own tables).  This had become too cumbersome as it was exceeding 90 repeats), as well as company data being duplicated in 5 or so other database files, so we are consolidating everything into a single file with 28 tables.  All repeating fields have been converted into their own tables.  The consolidation seemed to work, but this destroyed the Out field calculation in about 30 different ways, all of which have been successfully rewritten, except for this one part, where one of the things it has to output, is if the first character of the CURRENT record is different from the first character of the NEXT record, in the Shows::S_Show_E field. This is being done from within the MEDIA(SH) table and layout, because the output is almost all from the Storage# fields, which are unique to this table (no relations in any other table). It easily gets the first letter of the current record's "Shows::S_Shows_E" entry, and correctly identifies the current RecordNumber, but no method I have tried, gets the next record's "Shows::S_Code_E" entry.  In fact all attempts either return a "?" -- single question mark, or "" -- null. Removing ALL but this part of the calculation, this is one of the attempts: Left (Shows::S_Show_E;1) & " " & Get(RecordNumber) & " "& Get(RecordNumber)+1 &" " & Left (GetNthRecord(Shows::S_Show_E;Get(RecordNumber)+1);1) which returned "B 257 258 ?".  The B, 257 and 258 are correct (the 258 is just to make sure Get(RecordNumber)+1 works right), but for this record, the final output should be "C". Nothing I do in the GetNthRecord part does anything but generate a ? or null. I've tried getting rid of the +1 to see if it would return a B, but it's still a ?.  I've tried just "GetNthRecord(S_Show_E;Get(RecordNumber))" to return the full contents of S_Show_E, but it does not. There are currently over 20,000 SHOWS entries, expected to grow roughly 1,000 per year. The calculation field is exported to a text file after records are selected Reveal=="Y" and sorted in English by "Shows::S_Show_E" (all done by a button executed script). Of the properly returned 2305 records, all return "?". The Out calculation field is represented on the layout, as is Shows::S_Show_E. Have I missed something basic? Or is this due to the internal structure of the GetNthRecord function? Does anyone know of a simple way around this? Even if it takes a second calculation field?

      This_is_how_it_was_entered_and_still_appears_when_you_edit.png

        • 1. Re: Comparing Record to Record+1 in calculation field but only getting "?"
          philmodjunk

          I'm going to take a pass on this one. I just don't have the time to figure out what you said in that massive block of text. Perhaps you can re-post in a more readable format breaking it up into paragraphs?

          • 2. Re: Comparing Record to Record+1 in calculation field but only getting "?"
            schamblee

            It's hard to understand.  Going on the title.  If a field returns a ?

            1)  The field is not large enough to display the number, so make field larger.

            2)  The field calculation is not returning a number.  Test small sample of records, like two records to verify you are getting the expect results.
            3)  Make sure your field is of type number.  

            If these suggestion don't work, then I would suggest as PhilModJunk stated above, repost post as he suggested and I would use a larger font.  Smaller fonts seem to run together, my eye site is not quite what it used to be, which if it's broke up it wouldn't be so bad.

            • 3. Re: Comparing Record to Record+1 in calculation field but only getting "?"
              SeijoKoibito
               @PhilModJunk: Perhaps you could read the first sentence or two.  It WAS formatted.  This site stripped out all the paragraphs, CRs, etc.  I should perhaps capture and post as it looks while in edit mode?  This happened as well to about 8 other people while I was searching for a similar situation. @S Chamblee: 1. The field is 750 pixels wide and 2 rows tall. 2. No, it's returning text. Letter, space, number, space, number, space, question mark that is supposed to be a letter.  The actual string is a lot more complicated, but that first letter and last letter are the important part in a long string. 3. It can't be, it's returning a string that has a minimum of 8 pieces of information, including all three of the Storage fields, the name of the item (usually in English), and a few other things based on what is or isn't in the Storage fields. This did work when this was a FMP5.5 database (when it was created) it had no relational content, but loads of repeating fields.  It survived conversion to FMP10 where it worked exactly the same.  After setting up the data in it and several other files into relational tables, this one and only part is not working
              • 4. Re: Comparing Record to Record+1 in calculation field but only getting "?"
                SeijoKoibito
                 The image at the top, slapped together like newspaper clippings, was captured by going back into edit mode, and just grabbing the screen.  The formatting still exists when you click on edit.  So the site is messing something up (and still is). If you have a suggestion for making it work properly please let me know... as it's more annoying at my end. (See the "Textarea" notice under the first part of the capture?  I believe that is supposed to indicate that it is supposed to leave it formatted as if you typed it in a word processor).