Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
8 years ago

Is it possible to choose multiple selections from a lookup field to a parent table or write a formula that will combine them?

I have 3 tables: Trades, Tasks, and PO's. Trades (parents) have many Tasks (children) and PO's have many Trades and Tasks. In PO's we choose a property address and then a related trade (say Foundation). Then in PO's I have a conditional dropdown for Tasks based on the Trade name and related property address. The problem I'm having is that there can be several Tasks listed under the Trade Name but you could actually have all of the related tasks included on a single PO. If I enter a PO and add a Trade Name that has  only one Task assigned to it, that works. However, if I have a trade name that has multiple tasks, such as Foundation that can have 4 tasks associated with it and that PO includes all four of those tasks, I can't multi-select from that Task lookup field. I know this is not possible in QB.

The PO could relate to multiple tasks within that trade. If I need the PO's related to several trade tasks, how could I write a formula that maybe if I add a checkbox on the PO table where you could check that the Trade Name for the PO contains all 4 related Foundation Tasks I could set up a rule that shows a link to all 4 related tasks for that property. The reason for this is that I also need to be able to see the related PO's in each task in an embedded PO report in tasks.

I could be going about this all wrong, so any suggestions welcome!
Thanks!
Dawn
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    8 years ago
    It sounds like your tasks and PO's relationship is currently set up such that one task can have many PO's based on this: "in PO's I have a conditional dropdown for Tasks based on the Trade name"

    Because you need to have multiple tasks for a PO, you will need tasks to be a child of PO's (one PO can have many tasks). 

    It would also seem like you sort of have a standard list of tasks for each standard trade, and when creating a PO, you might want to populate multiple tasks from one or more trades. (but not necessarily all the tasks listed for a trade?)

    If what I described above is true, the solution you need is fairly complex and would probably require some offline help. 

    On thing that you could try that wouldnt require help is to create a PO tasks table, where a PO can have many PO tasks, a Trade can have many PO tasks, and a Task can have many PO tasks. On the PO task table you would now have a trade dropdown and a task dropdown (probably much like you currently have on the PO record) and you could make that conditional. Then you can add whichever Tasks you want to a PO. 

    Let me know if that helps. If you would like to explore other options, my contact info is in my profile.

    Good Luck!
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    Thanks Eric. I think this will require an offline conversation. :) I'll email you!