Hello. Is there anyone who is familiar with Allocation Schedule in SuiteQL please? I tried to filter out all allocation schedules using "frequency" column which values is "Run By Batch". When I check them in xml view, its value is "RUNBYBATCH" but if I run this sql, it does not show.

SELECT GeneralAllocationSchedule.frequency, GeneralAllocationSchedule.hasRemainingRemainders, GeneralAllocationSchedule.id, GeneralAllocationSchedule.name, GeneralAllocationSchedule.nextdate FROM GeneralAllocationSchedule WHERE frequency = "RUNBYBATCH"

I tried LIKE and string comparison but that didn't help me at all. But other frequency like "ENDOFPERIOD" works as expected. I don't think it automatically Joins but as you can see here, I filtered by value "ENDOFPERIOD" but in table, it shows as "End of Period"

There's a table called

frequencytype

that appears to line up with almost all of the frequency options on the schedule. The "Run By Batch" option doesn't appear in frequencytype, and the frequencytype option CUSTOM does not appear in the allocation schedule list. I ran the query

SELECT
  frequency,
  type.type
FROM generalallocationschedule
LEFT JOIN frequencytype AS type
ON type = frequency

to get a sense for what's under the hood, and I got this result:

{
  frequency: 'Run by Batch',
  type: 'NEVER'
}

Clearly, NetSuite is doing something a little funky here, as switching the Frequency in the UI to "Never" shows the same type value. With the above info in hand, you can run a query like

SELECT *
FROM generalallocationschedule

WHERE frequency = 'NEVER'

and your Run by Batch schedules will appear; however, any that are 'Never' will also appear. I don't see any other fields that are available via SuiteQL which appear to change when just the frequency is toggled between the two. Of course, you could fetch via 'NEVER' then filter your results in SuiteScript.

Thank you @Clay Roper

@Boris Yasen I don't think it's an exposed join, as it doesn't appear in the Records Catalog as such

Okay thank you

@Boris Yasen Also, there are a couple of columns in the

frequencytype

table, such as

longname

, that match the value shown in the frequency field on

generalallocationschedule

yes

It is odd for sure. There may be a different relationship somewhere that either I can't see or that isn't really exposed. I just found

frequencytype

when looking through tables in the Records Catalog and noticed the apparent relationship there.