Create a dbjoin multi-select from another dbjoin multiselect

[bespokeappstt]

Can I create a multi-select database join from another database join multiselect?
So this is the clearence list, whose column 'vendorpo' is a dbjoin of freight ops.


On the front the form looks like this:


And the list looks like this:


What I want is another list called Warehouse to be able to select the dbjoin values in clearance, under the column 'Vendor PO#' plus the Docket number. So join the columns of 'Docket Number' and 'Vendor PO#' of Clearence into one column in Warehouse. I'm trying to synchronize the data as much as possible and minimize data entry.

 

[juuser]

You may be trying to do some things in a much more complicated way than needed.

You have a "clearance" list and a "warehouse" list. I might get the idea totally wrong, but I assume you want to add records from "clearance" to "warehouse" list when the stuff arrives?

In that case you can create a dbjoin element (e.g. clearance_id) in warehouse table referring to clearance table's id. And then in clearance list add "list link plugin" to refer to warehouse form with additional querystring &warehouse___clearance_id_raw={clearance___id_raw}.

And then you can get the Docker # and Vendor PO with a calc element or join from clearance.id to warehouse.clearance_id

In general, it would be better if you could describe your workflows and goals. In that case it would be easier to suggest the best ways to get the things done rather than solving some complicated issues which maybe could be avoided in the first place with effective planning of the app structure.

 

[bespokeappstt]

Yes, you are right. So I'll give some context. We have 3 lists in play and they are filled in by 3 different people.. Freight, Clearance and Warehouse. It all starts with Freight and then rolls down hill so to speak. Freight creates a vendor po. Clearance has a dbjoin/multiselect to this vendor po created by freight. Clearance also create a 'docket' number that warehouse has to use. When it comes to warehouse, I want them to see only the vendor po's used by clearance along with the associating docket number created by clearance, and to be able to multiselect values.

So I'm thinking that this should be a simple dbjoin with a 'select' statement to concat the 'docket' number created by clearance and 'vendor po' used/selected by clearance. This is what I have thus far:


The docket number shows but not the vendor po selected by clearance.

 

[juuser]

You cannot have line breaks in multi-select dbjoin's concat label and you have several PO-lines per one Docket number, so it won't work like that. You would need to concat the PO numbers something like:
SELECT GROUP_CONCAT(vendor_po SEPARATOR ' | ') FROM ...

But, I think I'm still not fully getting your setup. As I understand, warehouse person marks received pieces for a customer PO. How does the logic goes if warehouse person can select multiple dockets/customer PO-s and there's only one field for pieces received. How will you know afterwards how many pieces from which order arrived. It could be e.g. "5 pcs from PO#1 and 5 pcs from PO#2" or "0 pcs from PO#1 and 10 pcs from PO#2" etc.

 

[bespokeappstt]

I was about to put a 'repeating group' of
po
docket
pieces

To solve that but it's okay. Let's assume it's not multiselect. I'll try out your, SELECT GROUP_CONCAT(vendor_po SEPARATOR ' | ') FROM ...

 

[juuser]

Repeating group with separate "received pieces" field for every PO would probably make sense.

But I would still build my setup like having the PO-s in the list as one PO per row. And then redirect to Warehouse form (e.g. list link plugin) with PO already pre-selected and user would only need to enter "received amount" for that particular PO.

 

[bespokeappstt]

Okay yes, what you originally suggested is great. I created a dbjoin on the docket number and created a link to clearance so that they can easily retrieve the po. This is what it looks like:

 

[juuser]

Now that's already starting to go to a right direction, but you still allow user to select the whole docket which can contain several PO-s so you still have a problem described above. I would make the database join element refer to a PO table's id where you have your PO-s listed and then only one PO could be selected. But again, I may not fully get what exactly is your goal. If you need to know how many pieces from the whole docket has arrived and don't care from which PO, then your solution probably works.

 

[bespokeappstt]

Just one last thing. That popup window disappears when you click on it. I want it so that the window 'only' closes when you hit the 'X'. How can I get that? I need the window to stay so that they can copy and paste the PO's. They guys in the warehouse only concern themselves with docket numbers. And the relationship between docket# and po is 1 to M. So when that window pops up, they want to be able to copy that po and paste into the repeating group field. I don't know why it just disappears when you click on it.

 

[juuser]

It is meant to close when you click on some row, because this window is for selecting the record to your Vendor PO# databasejoin element.

Copying manually PO-numbers to some other table field, hmm, I would really properly think through again the whole setup you have.

 

[bespokeappstt]

Humm. Okay understood.

 

[bespokeappstt]

Something strange has happened. I am trying to place the same look up for a vendor po field in a repeating group. Standard drop down. The dropdown works fine but when I hit the magnifying glass I get an error message, 'list is not published'.

 

[bespokeappstt]

forgot the actual join shot:

 

[bespokeappstt]

Okay disregard. Some strange reason it's all working now.

 

[bespokeappstt]

This is closed. Everything is working. My issues are solved. Will leave open for a few weeks for questions.