Workday requires inputs, which they call “prompts”, to successfully execute queries. The inputs are rarely intuitive values.
For example, to query journalLines, you need to use the following query:
SELECT *
FROM journalLines
WHERE company_Prompt = 'cb550da820584750aae8f807882fa79a'
AND year_Prompt = '2f54eb1728bf102c30f35c40194d0344'
AND journalEntryStatus_Prompt = '6f8e52d2376e4c899463020db034c87c'
AND entryMomentFrom_Prompt = '1/1/2017'
AND entryMomentTo_Prompt = '6/1/2017'
limit 10;
For company_Prompt, year_Prompt, and journalEntryStatus_Prompt, you must provide GUIDs.
This is different from the Workday UI, where it gives you a dropdown of friendly names to choose from as inputs. The Workday UI also does not seem to show the same prompts as are needed for the API.
Furthermore, the Workday UI does not give you the GUIDs of those values, so your only option is to query them from other tables via the driver. Unfortunately, sometimes those tables require prompts themselves.
To query journalLines, you must run two prerequisite queries first.
-- Query #1
SELECT [workdayId], [company.descriptor]
FROM companies
WHERE [company.descriptor] = ‘Foobar Industries’;
Use the returned workdayId from Query #1 as company_Prompt in Query #2, then:
-- Query #2
SELECT [company.descriptor], [company.id], [journalStatus.id], [journalStatus.descriptor], [fiscalYear.id],[fiscalYearNumber]
FROM journals
WHERE company_Prompt = 'cb550da820584750aae8f807882fa79a'
AND accountingDateOnOrAfter_Prompt = '1/1/2017'
AND accountingDateOnOrBefore_Prompt = '3/1/2017'
limit 10;
Use the returned journalStatus.id as the journalEntryStatus_Prompt and the fiscalYear.id as the year_Prompt, as shown in the query at the beginning of this article.
Please reach out to support@cdata.com if this does not resolve the problem for you.
