AnsweredAssumed Answered

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

Question asked by SeijoKoibito on Nov 5, 2014
Latest reply on Nov 6, 2014 by 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

Outcomes