Mastering Automate Studio Query: From SAP Data Extraction to Mass Updates

Name: Automate Studio Query Training: Unlocking SAP® Data Access, Mass Updates, and Reporting
Uploaded: 2026-02-17T13:37:52.205759+00:00
Channel: Precisely
Description: Summary and key takeaways on Mastering Automate Studio Query: From SAP Data Extraction to Mass Updates, covering Introduction Hunter Pogue, Sales Engineer at

Precisely

Feb 17, 2026

•

5 min read

YouTube video ID: g5zrl7hAvRo

Source: YouTube video by Precisely — Watch original video

PDF

Introduction

Hunter Pogue, Sales Engineer at Precisely, walks through Automate Studio’s Query module – a powerful yet often under‑used tool for accessing SAP data, performing mass updates, and generating scheduled reports.

Why the Query Module Matters

Ad‑hoc reporting – pull exactly the fields you need without writing ABAP.
Data maintenance – extract, edit in Excel, and upload back to SAP.
Automated reporting – schedule one‑time or recurring runs; results can be emailed.
Audit & QA – create traceable extracts for compliance checks.

Creating a Query – Four Entry Points

From Tables – manually select SAP tables (most common).
From Info Sets – pre‑defined sets that span modules; extra criteria can be added.
From Logical Databases (LDB) – business‑oriented collections of tables; criteria fields are fixed.
From a Recording – run a transaction once, let Studio capture the tables/fields used, then reuse them as a query.

Understanding Joins

Inner Join – returns only rows with matching keys in both tables.
Left Outer Join – keeps all rows from the left table and matches from the right.
Right Outer Join – not directly supported; achieve the same effect by swapping table order and using a left outer join.
Studio can auto‑detect joins on common keys and also offers a Fine‑Joining Table wizard to add intermediate tables when a direct join is missing.

Adding Tables & Finding Joins

Type table names directly (comma‑separated for multiples) or use the search panel (by name, description, field).
Drag‑and‑drop fields to create custom joins; right‑click to convert to left outer join.
Use the Data Dictionary dropdown to browse tables by subject area.

Selecting Output & Criteria Fields

Output fields = columns that will be written to the Excel result file.
Criteria fields = filters that limit the data returned.
Criteria can be runtime prompts, default values, or fixed values set by the developer.

Criteria Operators & Advanced Options

Operator	Description
=, >, >=, <, <=	Simple comparisons
BETWEEN / NOT BETWEEN	Range selection
IN / NOT IN	List of values
LIKE	Wild‑card matching
IS NULL / IS NOT NULL	Empty‑field checks
- Padding – choose none, space‑pad, or zero‑pad.
- Cell‑value criteria – pull a value from a specific Excel cell.
- File‑based criteria – link an external Excel/text file for dynamic IN lists (ideal for weekly‑changing material lists).
- Combine conditions with AND (narrow) or OR (broaden).

Testing Before Full Run

Number of Entries – returns a count based on current criteria.
Preview Run – shows the first 100 rows so you can verify joins and filters.
Troubleshoot by adding tables one‑by‑one and confirming expected SAP records exist.

Mapping Fields to Excel

Drag‑and‑drop SAP fields onto the desired Excel columns.
Auto‑Map can populate columns in the order they appear.
Restore Mapping resets the layout if you need to start over.
When using round‑trip scenarios, align the query mapping with the transaction script’s column layout.

Running the Query – Performance Controls

Choose Append, Overwrite, or Clear the result sheet before each run.
Define the start row/sheet to accommodate header rows.
Set record limits or let the query pull all rows.
Adaptive Query Throttling – protects the SAP system by pacing large extracts.
Data Chunking – split massive extracts into manageable blocks (hundreds or thousands of rows).

Scheduling Queries

Save the script and data file.
Open the Schedule Wizard → define criteria → set frequency (One‑time, Daily, Weekly, Monthly).
Optional Advanced Settings – run every 30 minutes for a set duration.
Enable email notifications with results attached; multiple recipients separated by commas.

Round‑Trip Mass Updates

Step 1 – Query: Extract SAP data into Excel.
Step 2 – Edit: Use Excel formulas, macros, or manual changes.
Step 3 – Transaction Script: Validate changes against SAP (live look‑ups, F4 help) and upload.
Backup Sheet: Studio automatically creates a sheet with original values for rollback or audit.
Use cases: material master updates, price changes, S/4‑HANA migration, purchase‑order quantity adjustments, sales‑order status changes.

Best Practices

Include only necessary tables, fields, and joins to keep performance optimal.
Always set selection criteria to limit data volume.
Leverage adaptive throttling and chunking for large extracts.
Preview data before publishing; use a separate “Instructions” tab for end‑user guidance.
Limit the number of tables per query (max 25) – split into multiple queries if complexity grows.
For text tables, add the language key as a criterion to avoid duplicate rows.

Resources & FAQs

Precisely Help Center, Support, and Precisely University e‑learning.
Community site (community.precisely.com) – free access to templates, tips, and peer support.
Common questions covered: finding SAP tables, when to use runtime prompts vs. fixed values, handling zero‑result queries, renaming Excel headers, building round‑trips for PO/SO, handling partial upload failures, extracting from custom Z‑tables.

Recap

Hunter demonstrated the Select → Map → Run workflow, explored joins, criteria, scheduling, and round‑trip mass updates, and provided a toolbox of tips to make the Query module user‑friendly for both technical and business audiences.

Automate Studio’s Query module transforms SAP data handling from a manual, error‑prone process into a streamlined, repeatable workflow—empowering users to extract exactly what they need, update records in bulk, and schedule reliable reports—all within the familiar Excel environment.

Frequently Asked Questions

Who is Precisely on YouTube?

Precisely is a YouTube channel that publishes videos on a range of topics. Browse more summaries from this channel below.

Does this page include the full transcript of the video?

Yes, the full transcript for this video is available on this page. Click 'Show transcript' in the sidebar to read it.

Why the Query Module Matters

- **Ad‑hoc reporting** – pull exactly the fields you need without writing ABAP. - **Data maintenance** – extract, edit in Excel, and upload back to SAP. - **Automated reporting** – schedule one‑time or recurring runs; results can be emailed. - **Audit & QA** – create traceable extracts for compliance checks.

Helpful resources related to this video

If you want to practice or explore the concepts discussed in the video, these commonly used tools may help.

Sap Data Extraction Book Recommended

Provides deep SAP data modeling concepts and practical extraction techniques, helping users design efficient queries now

Amazon →

Microsoft Excel Data Analysis Book

Covers advanced Excel functions and formulas that complement Automate Studio queries for powerful data manipulation

Amazon →

Data Integration Best Practices Paperback

Offers proven strategies for integrating SAP with external tools, ensuring smooth round‑trip updates today

Amazon →

Links may be affiliate links. We only include resources that are genuinely relevant to the topic.

Summarize another video

Full Transcript YouTube

Hello, my name is Hunter Pogue. I'm a
sales engineer here at Precisely. And
today I'll be giving an Automate Studio
query training that helps users unlock
access to their SAP data, perform mass
updates, and use query for reporting.
For today's agenda, I'll cover a brief
introduction of query. We'll step
through creating a query and the
different options available to
developers.
I'll talk about joins, the different
types of joins, when to use them, how to
use them, some tips and tricks. We'll do
the same with criteria, talk about
different criteria options, how this can
be used to limit the output of your your
queries.
I'll show a few examples of using query
for mass updates in a roundtrip
scenario. And then we'll talk about how
query can be used to create reports and
also the options we have for scheduling.
I'll also provide a few resources and
then if there's time I will answer a few
questions at the end.
As I speak with many customers about
their usage of studio, studio manager
and evolve,
I sometimes find that the query module
is a piece that is underutilized.
So the purpose of this webinar today is
to empower users to better understand
the query module, give some ideas of how
it could be useful for your day-to-day
use
and how you can realize the benefits
you're already seeing from transaction
module uh and from our other offerings
and incorporate that into your
day-to-day job
to help you better realize the benefits
of the automate tools.
Some potential uses and common uses of
the query module include creating ad hoc
reports, using it for data maintenance,
pulling data out of SAP, making changes
and then uploading that back for mass
update scenarios,
automated reporting which allows for
scheduling of regular reports or
one-time reports. And then these reports
can also be used for auditor QA
purposes.
When creating a new query, there are
four options for creation.
You can create from tables,
you can create from info sets, from
logical databases, or you can even do a
recording. And what the recording is is
you step through a transaction code like
you would for uh creating a script in
the transaction module.
Studio tracks the tables and fields you
interact with and then they use that as
the the tables and fields that get
selected for your query.
Let's dive a little bit deeper into some
more details for the first three
options.
When you're creating queries based on
tables, which is the example we'll look
at today,
there are a few things you need to keep
in mind. So, if you're selecting
multiple tables for your query,
understand the relationship between
these tables. What types of joins are
needed, whether it's inner joins or left
outer joins.
Understand the data. Know what it should
look like. That way, you're able to uh
accurately troubleshoot and diagnose any
issues.
We recommend opening a single record in
SAP and using that as an example.
Logical databases combine several key
tables based on business requirements in
an area. So criteria fields are
predefined and they cannot be changed.
And you use these when tables you need
are stored in more tables than you're
allowed to add to a query.
A good example of this would be for
sales data or inventory.
And just as a tip, you can also query
the LDBT table to see the list of
logical databases.
Info sets can be designed to gather data
across modules.
They include required criteria fields.
Additional criteria can also be added.
And you can use studio search
capabilities in the workspace tab to
search for available in info sets in
SAP. An example of this would be to
download sales data with inventory or
purchasing information.
Before
we jump into Studio to create our first
query, I wanted to touch quickly on the
types of joins that are supported with
Studio and give a visual for what that
exactly means when it comes to how your
data is presented.
The first is the inner join where you
have in this example a table with
customer data and a table with order
data.
You have excluded records in the
customer and the order side. And what is
displayed is the records with matching
values. That inner piece of that vin
diagram as you see on the screen.
If you convert your joins to left outer
joins, it'll display the selected
records from the left table and the
records with matching values while
excluding the records of uh whichever
tables on the right. Please note that
right outer joints also exist and it's
the opposite of this. Uh but right outer
joints are not supported with studio. So
if there's a case where you need a right
outer join, just simply switch the the
order of your tables, move the the one
that needs the right outer join to the
left and you can create a left outer
join to do the same thing there.
Now we're in Automate Studio and ready
to create our first query.
I'll navigate to the new tab, select
query, and then select create from
tables. From here, I'll log on to my SAP
system. So I'll click log on with my
named credentials.
Now that we're signed in, we'll go ahead
and click create script. If you have an
existing data file, you can also use
that. But in this example, we'll start
with a blank uh Excel file.
You can see here we have a workspace
that's blank currently.
When you think of the transaction
module, we often talk about the
three-step process that is record, map,
and run. There's a similar process when
creating queries, but instead of record,
map, and run, it's select, map, and run.
So, the first thing we'll do is select
which tables need to be in the query
we're trying to achieve. We'll select
which fields we want as our outputs and
our criterias and ensure that any joins
are in place.
There are several different ways to add
tables to your workspace. the first one.
If you're familiar with the tables
you're trying to add, you can do a quick
add.
By doing this, we can go ahead and just
type in the name of our table. If you
want to add multiples at once, you can
separate them using a comma and it'll
add them to your workspace. So, we'll go
ahead and just add the MARA table, which
is for material data.
If I'm unsure of the name of the other
tables I'm wanting to add to my query, I
can also do a search.
With the search functionality, you have
the options to search for table name,
table description, or a specific field
name, or field description. We can also
just leave it as all. And I know I'm
looking for some plant data.
So, I'll go ahead and type that in the
search.
We'll click search. And then you can see
it found the appropriate table here. We
can go ahead and select add table to
workspace. And now we have both. And you
see that it automatically added a join.
So when there are common primary keys or
good fields for for tables to be joined
to, it'll join them automatically.
If you're unhappy with a join, you can
either remove it by right-clicking.
To create new joins, you simply drag and
drop from one field to the next. And
then in a case where you're looking to
add a left outer join, you also right
click and you convert it to a left outer
join directly from this menu here.
For this example, we'll go ahead and add
one more table to get the uh material
descriptions.
You also have the option to find tables
under the related subject using the data
dictionary. So if we go to this
dropdown, select data dictionary.
Um so based on the subject, we can find
the associated tables and add those to
our workspace here.
Now that we've added all the tables we
want for our query, it's time to start
selecting output and criteria fields.
Before this, one thing I'd like to touch
on is some of the functionality that's
built in to assist users in finding
joins.
So, if you're having trouble finding uh
joints that work for the tables that you
select, we have a fine joining table
option, which allows users to find all
possible joins or even add an
intermediate table to link two tables
together that don't have a good field to
to join from. So, I'll show you what
that looks like quickly. So, if we're
finding joins, you select which tables
you're trying to join. So in our case,
we'll just look at this one for Mara and
oh
here we'll do Mara.
And here you can see all of the
available joins that are an option. Um
and then if you were looking for an
intermediate join, you would simply
select that option and it would find any
valid
tables to add to join these two tables
together. And then once you select the
one that you want, you click apply
selected joins and it'll automatically
join those tables on your workspace.
We'll go through and just select a few
fields for the example. So as our output
and criteria. So the output fields are
the fields that will be um pulled from
SAP and populated to your data file. The
criteria fields are the ones that you
can use to set the criteria and filter
the values that are being pulled out of
SAP.
So, we will go ahead and just select a
few. We'll pull material group.
Um, we can do the date it's created on,
who created it.
You also have the ability to search for
specific fields if you know what field
you're wanting to add. And this number
here just quickly tells you how many
fields you have selected as your output.
We'll go ahead and select a few fields
for the mark table. So we can do plant.
We'll add that as criteria as well.
We can do our ABC indicator, our
purchase group,
and then we'll want the material
description.
And for our example, that should be a
pretty good uh set of fields. Down
below, you can quickly see the output
fields we've selected, the criteria
fields we selected, and then details on
any joins we have.
So now let's move into one of the most
important parts of query, which is
setting the criteria options.
The criteria here determines what subset
of SAP data you want to return. And
without the criteria, Studio Studio will
return all records from the tables
you've selected. Um, that can easily
mean hundreds or thousands or even
millions of lines in some cases. So with
the criteria, it allows you or the end
user to filter, refine, and control the
performance of the query and the values
it returns.
Setting this criteria can be simple like
if you're wanting to just select maybe
one material type or one plant or you
can have it uh be very advanced using
and or conditions including ranges, wild
cards, or even values that are pulled
from a file or an Excel spreadsheet.
To start, each field you selected as a
criteria field has the option to use
basic operators. We'll quickly step
through these. So there's equals.
Um
there's your typical greater than,
greater than, equal to, less than, less
than equal to, between, not between.
um
in which lets you specify multiple
values and it's one of the most flexible
options.
There's not in, there's like which
allows you to use wild cards for
potential partial matches.
And then there's is or is not null,
which is a filter for empty or populated
fields.
Once you select an operator, you'll
enter the condition field uh value that
you'll want to use as part of this
filter.
These values can be either left open for
the end user to enter their own desired
values. It can be set as a default value
by the developer that can be changed or
you can have it as a fixed value where
the developer sets the value that's in
this field and the end user cannot
change it which can limit you know the
end user pulling data that they
shouldn't be pulling or pulling extra
data things like that.
So for example, if we wanted to have
equal to
F, so finished goods, this will only
pull finished goods back.
Of course, you have options for padding.
So if there is a need for padding, you
can do no padding, prepad with spaces,
with zeros, or post pad with spaces.
Additionally, as I mentioned,
the developer has the option to leave
this as a userdefined value where the
end user will specify the value at the
time of running the the query script.
You can use cell values. So any cell
value from your Excel file to pull in as
this value into your criteria
or it can be a fixed value as I
mentioned where the developer sets the
value that cannot be changed at runtime.
You have the option to use and or
expressions for any of the the fields
you choose as your criteria. So if we'll
navigate up here, you can see this plus
sign. If we add that, it'll add a
condition
and narrows the data. So all conditions
that you set must be met in order for
those values to be pulled from SAP as
part of your query. And then if you
select or
it'll broaden your search and any of the
conditions
can be met and then it'll pull those
values into your query.
So for example, if we want the options
to search from
maybe a certain material, right, you can
enter the exact material number.
Sometimes you might want a range or a
list, right? So if you want to do in,
you can provide a list of values and
it'll pull from that list.
We can even add a third and we could do
a range. So, if we wanted to do a range
of materials to search for, you have the
option to do that here.
You can also use file-based criteria,
which is pretty powerful uh when your
selection list is long or it changes
frequently.
This only works with in operators
because Studio is reading a list of
values.
This can be really helpful if you want
SAP to filter using a dynamic list such
as pulling materials from an Excel list
that changes week to week. So to do
that, you would select this option for
your list and then you could do a
dynamic lookup list and either use your
current data set file or a linked Excel
or text file to pull those values.
So we've gone over several criteria
options and as you can see criteria is
essential for both performance and accur
accuracy for your queries.
Limiting the extractions from queries
ensures that the right the right data is
being pulled. It avoids any unnecessary
SAP loads, reduces the time it takes to
run, simplifies your results,
and then later on down the road, as we
mentioned, it can support downstream
processes like round trips.
Developers can also choose to make any
of these criteria fields required. If a
field is required, the script will not
run unless there's a value entered in
that specific field uh when it's popped
up during the uh the runtime of the
query itself. So, we'll go ahead. We
made material type a required field.
There are two features you can use at
this point to see if you're kind of on
the right track with how you set up the
tables and how you set up your criteria.
There's preview run and number of
entries.
So from here I'll go ahead and enter a
range of materials.
So we'll do
just a range of of maybe 50 or 60.
to test it out.
And so if I'm expecting 50 or 60ish
materials to come back from this range,
we'll go ahead and click number of
entries. It'll use all the criteria
we've entered and simply bring back a
number.
If that looks right, you can move on. If
not, there's probably an issue with
either your tables, the joins,
or something in the criteria. So to
troubleshoot, we recommend adding one
table at a time and testing.
Um, then also you can check in SAP for
records to ensure that whatever records
you're trying to find are ones that
actually exist for the criteria you've
defined.
Another option here to kind of take a
quick look at your data without actually
running the full query is preview run.
So if we click preview run, this will
use the criteria that's defined and give
you a preview of the first 100 records
that match the criteria. So you can
quickly see uh whether the values that
are being brought back from SAP are
matching what you're expecting. And in
our case, it is.
So we're ready to move on to the mapping
portion.
Mapping is the point where we tell
Studio exactly which SAP fields we want
to bring back and how those fields
should be structured for our output. So
here you define which columns of your
data file should contain which field
from our query script and you can either
map these order they appear in the list
or you can decide which order you want
these fields to appear.
I'll go ahead and start mapping. Similar
to the transaction module, it's a simple
dragging and dropping of which column
you want each field to go into. Since
we're downloading data from SAP, you'll
drag from the field to the column. We'll
go ahead and log or map our log field
first followed by material number,
material type,
material group,
and then as I mentioned, you can kind of
order these any way you'd like as long
as you
have a column mapped. It doesn't matter
which order these are in.
You can see that you can also have the
type and length. So it'll tell you the
data type of each field and the maximum
length.
So finally we will do material
description. And now we have all of our
fields mapped.
If you're creating a script and you
would like to just map these fields in
the order they appear here in the list,
you can also do an auto map for a quick
uh a quick mapping of all of the fields
in this order.
In the case you want to restart the
whole mapping process, you can click
restore mapping and it'll erase the
mapping and go back to um how things
were just after you selected that
criteria
in the the output fields.
As I mentioned earlier, we did map to a
brand new Excel file, but in cases where
you're doing, say, a round trip or
adding this to an already existing Excel
file, you'll want to map these fields in
a way that makes sense, right? So if you
have a transaction script for material
change for MM02,
you'll want to ensure that your query is
mapped in a way where it's pulling the
values in for the existing materials in
the same columns that you have your
transaction script mapped to upload
those materials once you make your
changes.
So now that we've built our query,
selected our fields, applied all of our
criteria,
and we mapped it all to our Excel file,
the final step is actually running it.
Everything that's related to running
queries lives on the run tab. Here you
can preview results again, which is a
best practice before you actually run.
You can actually run the full script.
You can do a number of records at any
time.
You have a little bit of control over
the performance settings and you can
choose how data is written into your
results file.
So starting up in the top left, you have
the ability to
choose how the data will look in this
file. Do you want to append the data to
the results file? Um in in cases where
data is already populated on the file,
you can have it overwrite data in the
results file. Um and then the default,
which is what we have selected, is to
clear all map data from the results
file.
Here is also where you define
where the script starts. So depending on
the formatting of your Excel file, you
can determine which sheet this form or
this query goes to, which row the data
is being populated in. So in some cases,
you may have some formatting where you
actually want data to start populating
in row four or row five. You can define
that here. You can lock it so that each
time it's run, you're ensuring the data
gets put where it needs to go. And then
you can also define the number of
records that get pulled in total. So
it's defaulted to all but you can change
that if it makes sense for your use
case.
As I mentioned, you have an option to
view number of entries. Um
and you can also
where it makes sense do data chunks
with the automate function module. A
feature that comes with that is what's
called adaptive query throttling.
The purpose of adaptive query throttling
is to ensure that whatever jobs you're
running, whatever big queries you may
have, it's not going to impact the SAP
system that it's pulling from.
Similarly, if you have a large query,
there's the option to pull this data in
chunks. So it doesn't tie up the system
resources. So you can choose to have it
be pulled in chunks of, you know,
hundreds or thousands depending on the
the size of your query.
Now is also a good time to talk a little
bit about the scheduling options. So I I
mentioned these briefly before, but
we'll go ahead and save both our script
file and our data file.
And when you go to schedule a script,
you define the criteria.
Once you've defined the criteria, you
click okay. And then the scheduling
wizard pops up. So you can see we
already have our SAP credentials saved.
We're logged in.
You can have it send email notifications
with the results of the query run. And
if you want to enter multiple email
addresses, you can separate them by
commas.
If we click next, here's where you set
the frequency of the schedule. It can
either be a one-time schedule.
You can have it as a daily schedule or
set it for every so many days.
You can have a weekly schedule where you
have, you know, every week this query
runs on Monday and Friday at 5:00 pm,
just as an example.
And then there's also a monthly option
if you want to to set a date for it to
run monthly.
Now, under the advanced settings,
there is the option of having the task
run every 30 minutes for a certain
duration. Um, I haven't really seen that
in in practice, but it is an option
there.
So, we'll go ahead and cancel that and
go back to our running step.
So, like before, we can do number of
entries. And I'll quickly just do that
again just to make sure nothing has
changed here. So, we'll enter our
ranges.
Additionally, as an enduser,
runners will have the ability to open up
an F4 lookup. So, if they're trying to
enter in criteria, they're unsure which
values are valid, they can do an F4
lookup to see all of the valid options
from SAP directly from within Excel.
So, once you're happy with the criteria
that's entered, we'll go ahead and click
okay. And again, this is just for a
number of entries. And you can see still
54 entries is what we were expecting. So
we'll go ahead and click run. So once
again I'll enter my criteria
with our range.
We'll click okay. And this is actually
running the query for the first time. So
you can see it's found all of the
records that match our criteria and
populated all 54 records. Again it tells
us this number here of how many records
were downloaded.
And that is the full process of creating
a query. The three-step process of
select, map, and run.
While today's query was fairly simple
with only using three different tables,
you can also build more complex queries
that introduce additional tables.
But with this adds complexity. You want
to ensure you do proper testing
and you want to make sure that the joins
all make sense and they they aren't
going to mess up the query overall.
A couple best practices when when using
the query module only include necessary
tables, fields, and joins.
Anything extra may degrade performance.
Use Studio's adaptive query throttling
when you're extracting large data sets.
Always use selection criteria to extract
just what you need.
Use data chunking when you're extracting
large data sets
and preview the data before you publish
to confirm the results are as expected.
Next, we'll talk about one of the most
powerful capabilities in Automate
Studio, the round trip, which is using
the query module and the transaction
module together for mass updates.
Round trips allow users to extract SAP
data, modify that data within an an
Excel template, and then upload those
changes back to SAP all from the same
screen.
This setup allows users to perform these
mass updates while still maintaining
accuracy and governance
with the ability to do live validations
against SAP before uploading the data
back to ensure that there are no errors.
Round trips are good to use when you
need to do updates to existing data in
bulk. if you need to compare current and
new values side by side
and also it's a common use case for S4
migration activities since Studio allows
users to connect to mult multiple SAP
systems you can connect to your ECC
system extract data from that into your
Excel template
make sure the data is cleansed and
looking uh the way you'd like it to look
and then you can connect to S4 and push
that data into your S4 system.
Round trips are a closed loop process.
You can see the three steps for creating
a
query script. Select map run, which we
just saw.
Once the data is in Excel, you can
analyze it, change it, adjust it, make
whatever changes you may need and then
switch to a transaction script to load
that back into SAP.
So now I'll show you an example of a a
material roundtrip for material
maintenance in Excel itself.
Starting in the banner, you can see I'm
I'm logged into studio, logged into SAP.
This dropdown shows any scripts that are
published to this data file. So the
the query script that we built when
you're ready for it to be shared with
users, you can publish it to an Excel
file which embeds it into the file. So
the end users can see, you know, what
they can do with this
data file. So in this case, we have a
query script for pulling material data
and we have a transaction script to
upload those changes to SAP.
So we'll make sure we start with the
query.
Again, we still have the same options
here. We can do a number of entries. So
we'll do that before we pull our data.
Just as a best practice, I'll enter my
criteria.
So we'll enter division. We'll do plant.
You can do MRP type.
And as you can see in this particular
example, I'm only pulling things that
were created by me. And this is a
required field. So,
since that is entered, we'll go ahead
and click okay. And that should bring us
back a number just to give us a sense of
how many records we're expecting to
find.
So, we found 35. That sounds about
right. So we'll go ahead and actually
run.
So again, I'll quickly enter my
criteria fields
and we'll pull that data actually into
the into the uh Excel document here.
So we've downloaded all of our records.
If we scroll to the right, you can see
that this query pulls a lot more data
than the one we showed as the example.
Um, so you know, with these queries or
update scenarios, you can include as
many fields as you'd like from SAP.
So we'll go ahead and now that we're
done pulling the data, we can switch
over to our transaction script. You'll
notice that the banner changes, you get
a few additional options, and we'll
cover a few of those in just a second.
So we'll go ahead and make a few
changes, right? I'll plant an error just
to kind of show what the error handling
piece looks like.
So, we'll plant a purposeful error. We
can change a few
descriptions
here. We'll do here. We'll change some
descriptions just to have
some changes. We'll do some weight
changes
and we'll just do a few of these fields
as an example.
You also have the ability to do live
lookups. So if you don't know the
available options, I'll actually show
that once I I validate. So, as a best
practice, anytime you're using a
transaction script, it's good to
validate the data against SAP. What this
does is it checks for any errors and
it'll highlight those those rows to give
you the opportunity to fix that before
you actually do the upload to SAP. So,
we'll do a validation. You'll see if we
scroll back around here, our validation
log is populated with the messages. So
uh in most cases we'll see see success
and then where I planted that purposeful
error here we can see the the SAP error
message being brought back saying that
the input is longer than the field
allows.
So if we need to change this, we can
select the field, click lookup values
here in the banner
and our F4 lookup shows up here. So
we can scroll through the acceptable
values. We can do a search
if we're looking for a specific value.
In our case, the value we want is right
here. So, we'll go ahead and select it.
It'll ask us if we want to overwrite the
existing data. We'll say yes. And then
you have the ability to just revalidate
only the rows with errors to save some
time. So, in this case, it'll just check
to make sure our error is fixed. It'll
highlight it green, letting us know that
it was fixed.
And now we're ready to run this. Now,
another feature I'd like to quickly
highlight is this backup data option. If
we select this and we hit run, that'll
run the script. You'll see an additional
sheet
show up in this data file. So there's a
new sheet.
What this sheet does is it captures all
the values before changes
as kind of a fail safe. So if you if you
make changes that you didn't mean to
make, if you want to revert something
back or reference what the old values
were, you have the ability to do that by
going to this screen. So you can see
here that the old values were captured.
So if I wanted to revert back to the old
uh we could rerun the script here, copy
certain values to the other screen,
whatever is needed there.
So we'll go back. We can see that the
rows we made changes to were
successfully changed. The rows we left
alone were left alone. And that was an
example of a material roundtrip for
making mass updates to material data.
For another roundtrip example, I have a
price change scenario. So again, we have
a query script and a transaction script.
published to this template. We'll make
sure we're in query.
We'll go ahead and run. So, as you can
see here, we have defaulted values. So,
condition type, application,
deletion indicator, validity, start
date, and end date, release status, and
language key are all set values by the
developer. So, as an end user, in this
case, I have no ability to change those
specific fields.
I can go ahead and make sure I have the
rest filled out. I'll enter my material
number range
and we will run it.
As you can see, it brought back all the
data for these materials where I want to
make changes to the price.
And something to remember when using
Studio is since you're in Excel, you
have the full power of Excel. So you can
include Excel formulas, Excel functions,
really anything you can think of and
build those into your Excel templates.
For this specific example, it pulls in
the current price
from SAP.
And then I have this field here. If I
want to make across the board pricing
changes. So say I want to make a 5%
price increase.
You can see that the new amount has been
configured and all of these have been
given a 5% price increase. The same
thing works for valid from and valid to
dates. We're replacing the old values
with the new values.
And so again, we'll switch from the
query to the transaction script.
We can go ahead and validate this to
ensure that any changes that we've made
don't bring back any errors from SAP.
So we'll scroll back up to the top and
you can see it's validating each line
all success messages so far.
And so once that's done, we'll go ahead
and run it. As you can see, we also have
that backup data option selected in the
banner. So once I hit run, and each time
I run this, we'll get that additional
sheet created just as a a check to make
sure that, you know, we're making the
changes we want. If not, we can go back
and revert it or reference the old
prices.
So we'll go ahead and hit run.
You can see that sheet was created here
and it's changing our records. So,
we'll let that run. Our prices are being
updated. Our new dates are updated as
well.
and all of our records have been run
successfully.
With this example, I've also added a
summary tab. So, what this does is just
gives a quick view of all the materials
we changed, the old price, the new
price, the new dates, and then just, you
know, all of these materials have been
changed by a set amount of per
percentage. So, just a quick little
summary tab. So keep in mind as you're
using studio, you have the ability to
leverage the power of Excel, build in
formulas, build in, you know, macros or
whatever you may need to fit your
business use case.
So to recap what we've talked about
today, we stepped through creating query
scripts with three simple steps. Select,
map, run. We talked about the different
ways to create queries.
from either selecting the tables, using
info sets, logical databases, or through
a recording.
I demonstrated how you can use Studio's
built-in functionality to add tables to
your workspace, join them together, edit
joins, remove them, add new ones, or use
Query's built-in tools to help find
joins that would work, whether that's
direct joins or using intermediate
joins.
We step through the different options
for setting criteria. Defining criteria
allows for seamless end user experiences
so that end users who use the query have
a clear path to pull only the necessary
data.
We talked about scheduling options for
queries. So for regular reporting for
one-time scheduling and I'll also add
that customers with studio manager or
evolve
also have the ability to schedule
queries on the server which will relieve
your local desktop resources.
Finally, we combined multiple studio
modules in a roundtrip scenario that we
can use for simple mass update
scenarios. So, I hope the topics we
covered today maybe taught you something
you didn't know, gave you ideas for ways
you could use the tool moving forward in
your day-to-day business use.
We'll share this deck with you, but I
wanted to include some useful links. So,
I've included links to the Precisely
Help Center, Precisely Support.
We also have some self-led e-learning
courses with Precisely University.
There are support articles,
documentation for the auto automate
products,
and then if you'd like to take it a step
further and uh find out more information
about our instructor-led training
courses, please reach out to your
account executive and you can review the
training course material with the link
provided here.
You guys may or may not be aware of the
automate community as well. It's the uh
it's a site for you automate users to
interact with fellow customers, find
links to resources, and much more. It's
free. So, if you go to
community.precisely.com,
you can register as a customer. Uh and
once you have an account and you're
registered, you have access to the
discussion for automate where uh myself,
members of my team post tips and tricks,
interact and answer questions that
customers post and then you'll find that
customers also post tips, questions. Um
so it's a good place to collaborate and
learn.
Also on the Automate Community site, we
have the SAP module templates, which are
pre-built studio templates for users to
go and download. Um, but keep in mind,
you have to be logged in to access this
template library.
Okay, I'll answer a few questions now.
So, one question, if I don't know which
SAP tables to use, what's the fastest
way to find them?
Uh, for that one, I'd say there's the
the SAP data dictionary within query
itself. So, if you know which area of
SAP you're wanting to work in,
materials, finance, um, that's a quick
way to find them. Um, something I also
do if I can't remember the exact table
names for certain things, I just do a
quick Google search and that can get you
get you where you need to go. You can
also look at the technical information
in SAP if you're in a transaction code
or something. Um, but those are some
quick ways.
When should I set criteria as a runtime
prompt instead of a fixed value? Um, so
I'd say for this one, you would use a
fixed value if you know it's something
that's not going to change. So if you
have, for example, maybe a specific
plant that your runner will be
pulling materials from. Set that plant
as a set value that eliminates the
chance of them entering an incorrect
plant pulling data they shouldn't have.
And then for runtime runtime prompts as
end users need the ability to to enter
their own values. Um do that when
flexibility is needed. So you know
material ranges,
dates,
those types of things. So just kind of
think through
you know, how can I make this as easy as
possible for the end user by defining
set values and then what fields do they
need to have the ability to change later
on down the road as they're running it
regularly?
What causes a query to return return
zero results even though the table has
data?
I'd say for this one, you want to double
check your criteria. Make sure if you
have any and or or
set up for your specific criteria
fields, make sure that all of the uh
criteria is met for what you're looking
for. If it still doesn't work after you
double check that, I would check and
make sure that your joins are valid.
Maybe you need to test with uh removing
a few tables and then adding them one by
one to see which join or which table is
causing those issues.
Uh let's see. Can I rename the column
headers in Excel without breaking the
script?
Yes. So once fields are mapped, you
know, you get the default field name and
technical name that are included in that
header. But once these are mapped, you
can change the name of, you know, the
headers to make them a little bit more
user friendly. Maybe remove the
technical information if your end users
don't need that. that that much
information. Um, you'll just want to
make sure that whatever you change the
name to makes sense for whatever field
is mapped to that specific column.
Uh, let's see. Can I build round trips
for advanced processes like purchase
orders or sales orders? Yes. So, round
trips can pretty much be used for any
use case that transaction scripts can be
built. So use cases for sales orders.
Purchase orders are commonly used maybe
to change quantities or dates. Um you
can use it for financial use cases. So
yeah, in most cases you can use round
trips for any mass change scenarios.
Um how many tables can you have in a
single query? So you can have up to 25
tables per query. Um, I'd say
that's a lot for one query and has a
potential to, you know, cause
complexities, cause issues for it to,
you know, run slow. So, it may make
sense to break it up into different
queries. Um, just kind of depending on
the use case, but 25 is technically the
limit there.
I'm getting duplicate records when I'm
working with texts
uh for descriptions. How do I fix that?
So text tables uh it can sometimes bring
in duplicate records. A way to fix it is
to add the language key field as a
criteria. So sometimes those duplicate
records will just have different uh
languages for the texts and that will
cause it to bring in duplicates. So that
should fix the issue.
Uh what are some best practices for
making queries easier for business users
to use who might be kind of
non-technical?
I'd say for this just keep it as simple
as possible. Right? So the output fields
make sure that only the necessary output
fields are included. And then this kind
of goes back to that criteria question.
Right? wherever you can uh limit the
number of entries and runtime prompts
that users have for queries. So where it
makes sense have default values where it
makes sense have those set values. So
the end user is uh it's very clear what
fields they need to input before running
the query. And then you can also uh I've
seen a lot of times where there's kind
of a separate tab that has instructions
in the Excel template. So you can kind
of put rules or just whatever
instructions you need to help the end
user in a separate tab on that that
Excel template. So there, you know,
there are clear instructions on how to
use it.
Let's see
what happens if SAP rejects one row out
of hundreds during the upload in a round
trip. So if you have, you know, hundreds
of records or even thousands of records
and, you know, you miss an error and you
just run, you do a run and it comes back
with maybe one or a couple rows that
didn't get uploaded,
um, Studio will upload the rest of those
records and then it'll highlight again
those uh, rows with errors and then
you'll have the opportunity to go back,
make those changes, and you can just
rerun the rows that have the errors.
without having to rerun the entire set.
Can query extract data from custom Z
tables? Yes. So custom tables act just
like normal tables and for the most
part. So as long as your users have
access to the custom tables, um they
should be able to extract the data from
them using query as well.
So I'll go ahead and stop there. Um,
thank you all so much for taking the
time to attend today's session. I hope
it was valuable and I hope you learned
something. If you want to continue the
conversation,
um, you can visit precisely.com. There's
additional demo content. There are white
papers and case studies and then you can
also set up a a call with us. You can
set up personalized call. We can, you
know, talk about your specific use case,
put together a demonstration. Um, so
thank you