SwitchClient, ODBC and Parent Value

[m.mcmullin]

I'm defining some metadata fields, and have a drop down list that's populated from an ODBC data source.

Is it possible to access the selected parent value so I can use it as a variable inside my SQL statement?


Thanks,

Michael

[gabrielp]

I think you can select the parent of an XML node in Xpath by using "child_node/..". Not sure if that answers your question.

[m.mcmullin]

Thanks for the reply. Just to explain the problem a little more, I'm defining some metadata fields in a Submit Point, and depending on the value selected from a dropdown list, I need my SQL query to change.

For example, if I have a 'Job Type' dropdown with the values 'A', 'B' and 'C', and a user selects 'B', I need to populate another dropdown from a database using SQL similar to:

Code: Select all

SELECT name FROM Options WHERE JobType = 'B'

(but obviously the 'WHERE' clause wouldn't be hard-coded).

At the moment, the second dropdown is a child of the first, but I guess it doesn't have to be. Xpath sounds like an interesting approach: where/how would I add that value?

[gabrielp]

You know what, I've never used the Switch Client with database queries as you're discussing here. I didn't know that this was possible. What I have done, is processed jobs through SQL SELECT elements, which saves info as a dataset, then used those dataset values in a checkpoint. But in a submit point, I did not know this was possible. I'd love to learn more about how you are doing this.

Sorry I cannot be of more help.

[m.mcmullin]

In our case, we're just setting the Data values field of a Dropdown list to 'Define values from ODBC data source', and querying our MIS for whatever information we need. It works well for us, but it would work a lot better if we could make our SQL statements aware of other selected values!

However, using checkpoints might be an option (although I'd prefer to grab as much info as possible up-front). I'll check it out...

Thanks again,

Michael.

[sander]

I played around with the SQL statements in the Client to achieve the same thing, so you might be interested what's the result. The idea was to fill in a ordernumber and the other values are populated via SQL. I could not get it figured out so I'm using checkpoints too, like Gabriel.

But what I tried to create was done, only the other way around. Via my MIS files are attached to an order, these are all placed in a generic hotfolder which routes to a checkpoint. So instead of doing a submit file we do this action in our MIS, which results in a checkpoint which reacts as a submit point, more or less. In my case based on the filename I do a select where filename= and get my order info.
Done that, and afterwards realised it is unnecessary using resources to do a SQL request for every field. So now, when a file enters the flow I do one SQL statement and select all I want, attach as metadata and fill the fields. (Actually I'm first transforming the SQL log so metadata from a submit point and database connect look the same).

What are you trying to create? Maybe there's a different approach like my case.

[m.mcmullin]

Using checkpoints is interesting -- unlike the submit point, there's a lot more information readily available. It would definitely be a workable solution for us, although it would be great to avoid the extra step.

Here's what we're trying to create. In SwitchClient, we currently have a submit point to our main workflow that handles the majority of orders from our prepress department. This collects some basic information (destination press, imposition, etc), and works perfectly well. However, we also have a lot of bespoke flows for some of our regular customers. For example, one customer sends 4 different print formats each week, and we set up a flow especially for them, where the prepress operator simply picks the appropriate format.

We could keep going the way we are, but as the number of flows increases, it is becoming a little more difficult to manage. Instead, I was hoping to store a list of these custom workflows in a database, cross-referenced to a list of customers in our MIS, which I've done.

Ideally, I'd like to alter our SwitchClient interface slightly to include a dropdown list with, say, 'Standard Jobs' and 'Custom Jobs'. 'Standard Jobs' would simply keep the existing interface, while 'Custom Jobs' replaces it with another dropdown list with a filtered list of customer names who have custom workflows available. This is as far as I've got, but at this point, I'd like to display another dropdown with a filtered list of the custom workflows available. To achieve this, I need to use the selected value from the previous dropdown as a filter for this one. For example

Code: Select all

SELECT workflow FROM CustomerWorkflows WHERE CustomerName = '<dropdown list value goes here>'

From the suggestions given here, I could send users who select a customer to another checkpoint where they select the workflow from. This is easily achievable, as the customer name is now available in the job's metadata, but it does involve an extra step for operators, which I'd like to avoid if possible. Another alternative is to hard-code the SQL queries, one for each customer, and use the 'Show if Parent Equals' property to display them as needed. At the moment, that might be manageable, but I tend to feel uncomfortable deviating from the DRY principle.

I can't help feeling that the value I'm looking for should be available somewhere, but maybe not. Sorry for the long explanation, I think the interface I'm trying to achieve is simple enough, but takes much longer to describe in words

[gabrielp]

Everything you said makes sense to me and is in line with my understanding of how the Switch client works. I think hard coding the values or adding a checkpoint step are the only ways to tackle this.

This reminds me of our file submission process where we had a similar problem. We would want people to drop in files, and depending on the metadata entered, ask a few more questions. The way I solved that particular problem was I automated the submission of the files into Switch (removing the need for the submit point as the first step), and prepared enough metadata on my own so I could ask those questions in a checkpoint.

[dkelly]

Unfortunately SwitchClient does not allow one field to access the value of another, including its parent.

[sander]

Yesterday Enfocus gave me a visit, this was one of my questions to make sure.
Answer is negative, so think we have to wait @ Switch 14

[m.mcmullin]

Good to know it's not possible, I'll put my efforts into workarounds instead!

Many thanks to everyone who responded, I'm going to send this as a feature request to Enfocus. Not sure how much demand there is for such a feature, although I'd imagine it would open up a lot more possibilities for SwitchClient.

[lombert]

I think SwitchClient should have the most update when Switch14 arrives, it's a lot of things that I think they can do better or improve with the client.

[Eightyk]

Is this possible yet, I am wanting to do the same thing.

Essentially using a Parent value as a variable to filter results in a dropdown list populated by ODBC.

If it is not possible yet, I'd like to add my name to the feature request.

[jan_suhr]

Is this possible yet, I am wanting to do the same thing.

Essentially using a Parent value as a variable to filter results in a dropdown list populated by ODBC.

If it is not possible yet, I'd like to add my name to the feature request.

This is a 7 year old topic.

You can easily solve this today with the Client Rest API and a PHP webpage that calls the database.

[Eightyk]

Thank you Jan for the idea.
I was hoping after 7 years it would be something that was added.
I get that it could be done outside switch, but i was hoping my users (employees) could stay within the switch web portal in a submit point.

Initially my thought was employee types in (for instance) a Last Name in the first metafield, (essentially acting as a filter)
Second metafield is a drop down with list populated from database with any person in the database having that name.
After picking from the drop down, it would give them the opportunity to submit new data to associate to that person in the database.

If Switch team were to add this as a feature, in an abstract way I could see it maybe working similar to the way variable datasets for pitstop server action lists work.