4 Replies Latest reply on Jan 23, 2015 1:39 AM by BenOtto

    (GetField) get field value from (undirectly) related table

    BenOtto

      Title

      (GetField) get field value from (undirectly) related table

      Post

      Dear community,

      I would like to pull/get/read some values from a related table and store it into a local text field. Important of me is:

      1) I don't want to use a (unstored) calculation / formula field because I want to "store" the value ... e.g. in order to let my audit log recognize changes made.

      2) the two tables are not directly related through one shared link but indirectly through a third table. I'll describe why in the example below.

      Now here is my problem:

      I can get a field value (let's say "seatNumber") from a direct relation, e.g. via the GetField() function. So my "Sold tickets" table can get the value from the "customer theater seat" table. But the function doesn't seem to work once I have that third table as a linker or hub. Meaning, I can't get the "seatNumber" stored in the "customer" record.

      Any idea how to solve that?

      Explanation of the example provided:

      Assume I want to sell tickets for some seats (movies, theater, whatever). Everytime I sell a ticket, I'll create a record in the ticket table storing the customer id and the seat id - and I will make sure that each of those two values are unique in the "ticket" table, so I don't get some overbooking. Once I delete some customer (e.g. decided to do something else ..) or remove a seat (..found out it's in a bad condition) , the ticket will be deleted automatically via the links. Or if I just delete the ticket, in all cases each entity (customer, seat) will be released automatically without complex scripting of record updates.

      Now I want to audit log my customer records. But formula fields / unstored calculations are obviously not recognized. So I need to store my seat number directly in the customer record via some calculation ...

      .. and here starts my problem described above. 

       

      PS: I have an example database that I can provide. Is it possible to upload such files here in the forum?

      Bildschirmfoto_2015-01-22_um_14.32.30.png

        • 1. Re: (GetField) get field value from (undirectly) related table
          BenOtto

          Here is another screen image.

          • 2. Re: (GetField) get field value from (undirectly) related table
            philmodjunk

            I think the problem is with your relationships and GetField is not the correct function to use here.

            Set Field [ YourTable::Field ; Get field ( "Customers Theater Seat::SeatUUID") ]

            and

            Set Field [ YourTable::Field ; Customers Theater Seat::SeatUUID ]

            will produce identical results. And I see no reason not to use unstored calculations here either. Unstored calcs will automatically update at the moment your layout or a script references them.

            But I do see a problem with the match fields in your relationships.

            A customer might purchase many tickets--even if by "customer" you mean the person that will sit in the seat, the same customer can purchase tickets for more than one event. So the relationship from customer to Tickets is one to many. Relation_CustomerUID should NOT auto-enter a UUID or serial number. This should be a simple text/number (UUID/SerialNumb) field that gets matching values from Customer_UID.

            In similar manner, a given seat can link to many tickets--one for each event. Thus, relation_SeatUID should a simple data field that gets a matching value from Seat_UID. (The lack of crows feet indicate that this is not what you currently have.)

            Another problem is that "remaining theater seats" appears to be a table separate from customer theater seat when it should, in most cases, be a second Tutorial: What are Table Occurrences? with the same data source table as customer Theater Seat.

            Of course, this may be a case where you have put together an example that does not correctly match your actual project. If so, I suggest describing your actual project so as to avoid such a possible mismatch in the future.

            • 3. Re: (GetField) get field value from (undirectly) related table
              BenOtto

              Dear Phil,

               

              you are as quick as ever.

              Actually yes, the example is does not match the project perfectly, so from your point of view of course we might assume 1:n relations while in truth it really is a 1:1 relation. Obviously trying to keep it simple backfired a little bit. smiley

              The correct setting is more like having multiple items stored in defined storage bins, so one item can only occupy one storage bin at the same time and each storage bin can only be occupied by one single item. 

               

              Now to the definition of my fields: Fields called "xxx_UID" are the primary keys generated using "Get (UUID)". Fields called "relation_xxxUID" are the foreign key references to those fields. In the example above when I create one of those "linkage records", here alias "tickets", I just copy my customer_UID into the relation_customerUID field and respectively the seat_UID into the relation_seatUID field of my ticket record. That leads to my unique match via the relations because the "uniqueness" definition of those values in my ticket table.

              The reason I want to automatically copy something like a Name_value (here "seatNumber") to my primary record is:

              1) Simple names are better than having to deal with some names such as "DVHC11-3GR5HC9P-XXX". The unique keys shall be used in background but I want to use simple names, that even can be change without having to adjust all relations.

              2) I want to avoid as much "scripting" as possible so I can remove the links as quick and easy as possible. copying two values in the linkage record ("ticket") is much simpler and less error-prone that copying all further values hence and forth in a script. Deleting my ticket instantly removes any connection between the customer (in reality item) and the seat (in reality storage bin).

              3) You are correct: Unstored calculations do the job perfectly in getting my "seat"-values into the corresponding "customer"-variables of record. But unstored calculations seem to be not recognized by audit log system such as UltraLog. I need some text variable here, where the value is updated when the record is committed.

               

              Now a question to your suggestion:

              Set Field [ YourTable::Field ; Get field ( "Customers Theater Seat::SeatUUID") ] 

              is a script step right? Because Set Field is s script command that I can't use in the calculation within the variable itself.

              How do I get a similar step into my field definition (alias automatically calculate value)? 

               

              Thank you so much. smiley

               

              PS: addition to point 2:

              The linkage record from my point of view offers a very simple way for linking the different records because:

              1) I can define the uniqueness (1:1) in the field definitions, so i can capture the errors easily in any script that tries to "overbook" some values.

              2) I can perfectly remove any relation between two records with one single action via

                     
              • deleting the linkage record (ticket)
              •      
              • deleting the customer (or item) ... the relation definition will automatically delete the linkage record 
              •      
              • deleting the seat (storage bin) --- the relation definition will automatically delete the linkage record 

              Now need for further complicated script steps.

              • 4. Re: (GetField) get field value from (undirectly) related table
                BenOtto

                One further point: as displayed on the second image: I CAN copy the values via GetField () from the seats to the tickets table. But I CANNOT copy it to the customer table: Neither from the "ticket" copy nor directly from the the seats table field. 

                Why?

                That's what I don't understand and what I would like to fix.