0

Complex Select statement for View

Summary of what I need to do:

I have a table called Actions within which I am trying to create a view of all actions which are compatible with the current action. 

Details:

An Action is undertaken by an Entity. The Entity performing the Action is considered the Actor of the Action, and the Entity the Action is being performed on is considered the Target of the Action. Entities are composed of different parts, and an Action needs access to a specific set of parts from the target Entity in order to perform the action.  

Every Entity is always engaged in an action, either as an Actor or a Target.

An Actor can only perform one action at a time, but can be the target of another Actor's Action. So an Entity can be engaged in two actions at the same time, one as the Actor, and the other as the Target.

So, when comparing two Actions for compatibility, the target of the first Action could be either the Actor or the Target of the second Action. 

Two Actions are considered compatible when the parts set the first action needs access to is available from the Target of the same action.

The parts sets are just that, sets in the mathematical sense in that some overlap. So, for example, there is a set that is comprised of all the parts, and there are others that are comprised of some of the parts. So for each set of parts, I need to know which other sets it's compatible with. There's only nine different sets, so what I did is I created a PartsSets table which holds the nine different sets of parts. I have a second table called PartsSetsCompatibility which contains a reference to the PartsSets table named PartsSet and a multiple choice(dynamic) field with a dynamic value of select PartsSets named PartsSetCompatibility. This lets me go through the nine sets one at a time and select which of the other eight sets it is compatible with. As an example, the set comprised of all parts is only compatible with the set comprised of no parts (there are a few actions which don't require any parts). 

So, back to the Actions table. Within it have (among others) the following fields:
ActionPartsNeeded: This is the set of parts required to be available from the Target of the Action in order for the Actor to perform the action. It's a link to the PartsSets table. 
Actor: ID of the entity performing the action
ActorPartsAvailable: Another link to the PartsSets table, this time showing the set of parts the actor leaves available while performing the action. 
Target: ID of the entity the action is being performed on
TargetPartsAvailable: Another link to the PartsSets table, this time showing the set of parts the Target leaves available while the Action is performed on it.

Problem restated:

OK, so within each record of the actions table I need a view listing all the other actions which it is compatible with and I'm trying to figure out how to create the select statement for this view.

Select Actions where ActionPartsNeeded is Compatible with the PartsAvailable from either the Actor or the Target of the current Action, depending on which is the Target. This is where I hit a brick wall.

I thought of creating a user defined function as a field in the actions table that I could use in the select statement as follows, while the syntax is eluding me it would look something like this:

Select Actions where isActionCompatible(Actions.ActionPartsNeeded, Actions.Target)

function isActionCompatible(ActionPartsNeeded: number, ActionTarget: number) do
    (I really struggle with Ninox's syntax so this is sort of pseudocode, hopefuly its actually doable).
    if ActionTarget = Actor {
        if ActorPartsAvailable = ActionPartsNeeded.PartsSetCompatibility (how do you compare against a multi-choice field?) then return true else return false
    }
    else /*ActionTarget = Target*/ {
        if TargetPartsAvailable = ActionPartsNeeded.PartsSetCompatibility
        then return true else return false
    }

end;

Anyway, I know this is pretty complicated, so huge kudos in advance for anyone who offers to help (hopefully I've provided enough detail).
 

19replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Mike N.
    • Mike_N
    • 2 mths ago
    • Reported - view

    I left something out and I couldn't figure out how to edit my post, so I'm appending this:

    There are only three Entities, therefore, an Entity can't be engaged in more than two actions. So, when checking to see if an Action an Entity wants to start (as the Action's Actor) is compatible with another Action, the other two Entities are either the Target or the Actor of the Action I am comparing against.

    Like
    • John Halls
    • John_Halls
    • 2 mths ago
    • Reported - view

    Hi Mike

    It all sounds so intriguing. I'd like to have a look at this but worry that I might spend an hour so two barking up the wrong tree. Can you give me a couple of examples? And maybe also show an expected output. If you could share the database, even better.

    Thanks John 

    Like
      • Mike N.
      • Mike_N
      • 2 mths ago
      • Reported - view

      John Halls So happy you're willing to look at this! I can't share the actual database but I could pretty easily create a simplified version with just the parts within this scope and share that (It's built in the IOS app so I need to figure out how to export it). If I run into issues then I'll screenshot the table schema and the tables to show the records I populated the tables with.

      Like 1
      • John Halls
      • John_Halls
      • 2 mths ago
      • Reported - view

      Mike N. Thanks Mike

      Like
      • Fred
      • Fred
      • 2 mths ago
      • Reported - view

      I to am intrigued by your question.

      To save a copy, at the start of the program with the DB list, if you click on the down arrow on the top right corner of the DB, you will see the option to Save archive as...

      Like
      • Mike N.
      • Mike_N
      • 2 mths ago
      • Reported - view

      Fred Thanks, again, I am using the IOS version. Within the database inside the tables list there is an export option which lets me export as a csv, excel format, or Ninox. Unfortunately I can't get the Ninox export to work, it ha a grid with each table having option to export data model, data, and files, but "select All does nothing and there is no way for me to select them individually. I can export as a CSV though so I will do that.

      Like
      • Fred
      • Fred
      • 2 mths ago
      • Reported - view

      I too use the app.

      Exit your DB but not the app. You will see a list of all your DBs. Then follow my previous instructions.

      Sadly exporting a CSV will not help us.

      Like
      • Mike N.
      • Mike_N
      • 2 mths ago
      • Reported - view

      Fred Oh, there were no down arrows on the database icons until I tapped on one. Thanks for your help with that Fred! Database attached. Within the Actions table there is a view with no formula, that's where I am trying to show compatible actions. I added tool tips in the three different table reference fields which refer back to the Parts table.

    • Mike N.
    • Mike_N
    • 2 mths ago
    • Reported - view

    I put the example together a little too quickly and missed checking a couple compatible parts sets with the Parts Set Compatibility record. 

    Lower parts - Gimbal should also be compatible with Lower Parts + Head

    and No Parts should be compatible with all others (I missed checking All lower parts for that one).

    Things probably make a little more sense with those changes but it would just make the result sets a little different.

    Like
    • Fred
    • Fred
    • 2 mths ago
    • Reported - view

    Hi Mike -

    Can you give us an example the results of the found set?

    For example if I'm in the Flare Gymbol 3 record in the Actions table, what records should I see if the view element has the proper select statement?

    Like
    • Mike N.
    • Mike_N
    • 2 mths ago
    • Reported - view

    I probably could have done a better job picking my examples, but assuming you made the two changes I mentioned yesterday in Parts Set Compatibility table (Lower parts - Gimbal should also be compatible with Lower Parts + Head, and and No Parts should be compatible with all others, i.e also compatible with All lower parts). Also, I just realized I forgot a rule: Compatible actions must include all 3 entities. Said differently, if an entity is the target of an action, the targeted entity can't target the actor of the same action. A couple examples, these are not allowed:

    Going through the examples marking which are compatible with which I just realized I need an extra field. The action parts needed field shows the parts needed from the target, but we also need parts from the actor and those two must be available. I apologize for not thinking this all the way through (it's been a bit of a challenge figuring out how to make it all work). Let me add that field and populate it with the correct data and repost the database.

    Like
    • Mike N.
    • Mike_N
    • 2 mths ago
    • Reported - view

    OK, updated database attached.

    restating the rules for compatible actions as methodically as I can:

    1. Rule out all actions which have the same actor as the current action.

    2. Rule out all actions which don't include the entity not in the current action. (if the action includes entities 2 and 3, a compatible action must include entity 1 (can be actor or target).

    3. The parts needed by the actor and target from the compatible action must be available from the any entity in the current action. The Parts Set Compatibility table shows us which available parts sets work with the needed parts set. So, if we need all lower parts, and all parts are available, that works as can be seen in the all lower parts record in that table.

    Expected outcome for a few records:

    1 rework 2 compatible with: 3 hammer 1, 3 idle 1, 

    2 flare head 1 compatible with: 1 idle 2, 3 idle 1, 

    1 spin upper 3 compatible with: 3 spin lower 2

    1 flare gymbol 3: 2 spin upper 1

    • Fred
    • Fred
    • 1 mth ago
    • Reported - view

    Ok, so I've worked out what I think is how to do the first two steps.

    let t := this;
    let selActions := (select Actions);
    let xAct := selActions[Actor = t.Actor];
    let xTar := selActions[Target = t.Target];
    let xSel := selActions[Actor != t.Actor and Target != t.Actor or Actor != t.Target and Target != t.Target];
    let step1 := xSel[(var r := this;
                count(xAct[= r])) = 0];
    let step2 := step1[(var r2 := this;
                count(xTar[= r2])) = 0];
    concat(step2)

    The variable selActions finds all records in the Actions table. Best practice says it is best not to do anymore select statements then necessary. Most people would have probably written a select statement for each of the variables on line 3 - 5. (Maybe I did too 😂).

    Then the xAct variable finds all records where the Actor equals the current Actor.

    The xTar variable does the same for Target.

    The xSel variable finds all records where the Actor in the current record is not an Actor or Target in another record and does the same for the Target. This will be our base.

    The variable step1 takes the array of records in xSel and subtracts any records found in xAct.

    Variable step2 then takes the results of step1 and subtracts any records found in xTar.

    The last line just displays the records left.

    Now that just leaves me to your step 3. And it is what I can't wrap my wee brain around. If you can clarify it a bit more. Can you put in terms of the db. If this field in this table equals to this field in that table, etc.

    I took your examples and I don't think the 2 Flarehead 1 gets the record 1 and 7. My formula shows that 1 doesn't show up. The other examples work, so I don't know if maybe there is some mistake there.

    I've attached the DB with my new fields so you can see how I went about figuring it out. I create a few new fields to see what results I get so I can see if I'm on the correct path.

      • Mike N.
      • Mike_N
      • 1 mth ago
      • Reported - view

      Fred Thanks Fred!

      I'd actually progressed with my solution where I created several functions which return a boolean and serve as filters to narrow down the data, then I used those functions in a select statement. It works ok but my functions are a whole ton of nested if statements. It's pretty complicated and probably inefficient (but I have less than 200 actions so efficiency wasn't a big concern).

      So, in the spirit of teaching a man to fish instead of feeding him, this is really helpful. For instance, I didn't know you could filter an array by putting conditions or functions into the index. 

      I get lost though on step1 and step2. 

      It seems like you are counting records from the array which holds the records where actor is same as the current record and then comparing that result to 0? Can you break this down for me and tell me how these statements work?

      and then the last statement, concat(step 2), is that how you take an array and present it in a table? (I was wondering how that was done).

      Again, thanks so much for taking the time to help me! As far as the results, if only one is not working I probably messed up on my analysis (I was doing it all in my brain as I wrote the question). I made up those action examples because I've had to simplify my actual tables and obfuscate the data somewhat, so, yeah, there's a chance I messed up on what the results should be, if all but 1 worked I'd say I probably didn't think something through correctly.

      I've also needed to change a couple things, so I'm working with some different fields now and there's actually another condition I had to add, but the basic problem remains the same: how to filter a results set that has a whole lot of different conditions and with a little more understanding of how to work with arrays to do that I think I can get where I need to go.

      Like
      • Fred
      • Fred
      • 1 mth ago
      • Reported - view
      Mike N. said:
      I get lost though on step1 and step2. 

       I'm still learning what is really happening as well. I'm just parroting what I've seen. You can try this post for more background. But basically I have the full array of records that I need to subtract the Actors and Targets from. So I'm counting any records that appear in xAct or xTar but don't appear in xSel (the = 0 part), then keep it. It is sort of a backwards way of finding records that don't exist in an array.

      The last statement of concat is to show the record Id in a field. In a view you would not need this. If you do a simple select statement (select Table1) in a formula field it will not show anything. So you have to put concat in front so Ninox knows to make a text field out of the results.

      Like
      • Mike N.
      • Mike_N
      • 1 mth ago
      • Reported - view

      Fred so how do I put the results (array) back into a table? If I just delete the concat statement the table (view) is empty. 

      Like
      • Fred
      • Fred
      • 1 mth ago
      • Reported - view

      You can do 1 of the following at the very end:

      step2
      

      or

      step1[(var r2 := this;
                  count(xTar[= r2])) = 0];
      

      Basically you need to have an array at the end.

      Like 1
    • Mike
    • ninox.1
    • 1 mth ago
    • Reported - view

    Why isn't there an ANSI standard SQL supported with an interface rather than this confusing proprietary "SELECT" approach?

    Like
      • Sean
      • Sean
      • 1 mth ago
      • Reported - view

      Mike My guess is because Ninox isn’t an SQL DBMS

      Like
Like Follow
  • Status Answered
  • 1 mth agoLast active
  • 19Replies
  • 200Views
  • 5 Following