7 Replies Latest reply on Jul 18, 2012 9:12 AM by philmodjunk

    Graph values from a selectable list of records from another table



      Graph values from a selectable list of records from another table


      Hello all, first post.

      I'm creating a database that will contain data for numerous lasers made by the company. These lasers vary in frequency range. I'd like to overlay their frequencies ontop of a graph of the atmospheric transmittance.

      My tables with relevant fields are as follows (QCL stands for Quantum Cascade Laser):



      _id : primary key

      frequency_low : Number - the low end of the range of frequencies achievable by this laser

      frequency_high : Number - the high end "



      _id : primary key

      qcl_id : Number - Global foreign key to a qcl, I'd like to make this a list of values. Currently I use this to select a single QCL to graph.

      wave_number : Number - the x-axis of the graph. These numbers multiplied by 0.03 give the frequency in THz, which are the same units in qcls::frequency_low and high.

      transmittance : Number - The y-axis of the graph. The ratio of transmitted light to incident. a value of 0.75 means 75% of the light is transmitted through the atmosphere. These values come from published literature.

      frequency_indicator : Calculation - 1 or 0. Here's my current calculation:

      (qcls::frequency_high + qcls::frequency_low)/2 ≥ wave_number * .03


      (qcls::frequency_high + qcls::frequency_low)/2 < GetNthRecord ( wave_number ; Get ( RecordNumber ) +1) * .03)

      it basically says, if the average of the QCL's frequency range falls between this record and the next, it's 1, otherwise 0. This has the effect of giving a vertical line on the graph where a QCL's average frequency lies so we can see how that fits with the atmospheric transmittance signature.

      minimum, maximum : Number - these two fields indicate the range of the spectrum the user wants to graph.


      I'd like to be able to define which QCLs are taken into account by the frequency_indicator field. I'd like to have a list next to my graph where you could go through and press a button to have a laser's average frequency indicated on the graph, and a button at top to include/exclude all. Lastly, I'd like to have the indication of which QCLs to graph be client-based so more than one person can operate the database at one time and each person select a different list of QCLs.

      Any ideas? I've been bashing my head against the wall on this for a while now. Thanks so much for any help.

        • 1. Re: Graph values from a selectable list of records from another table

          If wave_number is the x-axis and transmittance is the y-axis, how deos the value in frequency_indicator become visible on the graph?

          • 2. Re: Graph values from a selectable list of records from another table

            My mistake, I'm graphing both. Transmittance is a range of 0-1. The frequency_indicator is 0 or 1, 1 indicating that the frequency of the QCL is in that position on the x-axis. This allows us to see how well the light from that qcl will be transmitted by the atmosphere.

            • 3. Re: Graph values from a selectable list of records from another table

              Here's an example of the graph:



              the orange line is the atmospheric transmittance, the blue is the frequency_indicator

              • 4. Re: Graph values from a selectable list of records from another table

                I'm uploading the image to the forum using the upload an image controls below Post A Answer so others can see it more easily:

                You appear to have this relationship in place:

                atmospheric_data::qcl_id = qcls::_id

                and qcl_id is a global field. That's a good start as data in this global field will be specific to each user. Different users can place different values in a global field and other users will not see the value of the global field change in their sessions.

                In Manage | Database | relationships, make a new table occurrence of qcls by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be All_qcls.

                We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                Add it to your relationships like this:

                atmospheric_data::anyfield X All_qcls::anyfield

                Using the cartesian join operator instead of = means that any record in atmospheric_data will match to all records in All_qcls. Place a portal to All_qcls on your layout to get a portal listing all qcls records. While in layout mode, select the fields you choose to place in the portal row and use button setup to turn them into a button that performs this script:

                Set Field [atmospheric_data::qcl_id ; All_qcls::_id]
                commit record

                Your "select None" button would use:

                Set Field [atmospheric_data::qcl_id ; ""]
                commit record

                Your "select All" button would use:

                Set Field [atmospheric_data::qcl_id ; List ( All_qcls::_id )]
                commit record

                The list of values will enable your original relationship to match to all qcls records, but your frequency indicator calculation will need to be changed in order for it to work with such a list of values. I'll describe that approach in my next post here.

                • 5. Re: Graph values from a selectable list of records from another table

                  First, let's add a calculation field to qcls that computes the mid-point between low and high frequencies so that we can use this value in a relationship between atmospheric_data and yet another occurrence of qlcs.

                  cAvg_Freq : (frequency_high + frequency_low)/2

                  Define a calculation field in atmospheric_data to get the next wave number:

                  cNextWaveNumberTHz : GetNthRecord ( wave_number ; Get ( RecordNumber ) +1) * 0.03

                  and another so we get the right units for comparison:

                  cWaveNumberTHz : WaveNumber * 0.03

                  Define this relationship:

                  atmospheric_data::cWaveNumberTHz < qlcs_byFreq::cAvg_Freq And
                  atmospheric_data::cNextWaveNumberTHz > qlcs_byFreq::cAvg_Freq AND
                  atmospheric_data::qcl_id = qcls_byFreq::_id

                  Now your calculation for the frequency indicator can be:

                  Not IsEmpty ( qlcs_byFreq::_id )

                  Note, however, that this assumes that the frequency indicator will return 0 or null for at least one value between each non zero result or you wont' get the nice 'spikes' shown in your screen shot.

                  • 6. Re: Graph values from a selectable list of records from another table

                    This is fantastic. Thank you so much for the help. How could I modify the

                    Set Field [atmospheric_data::qcl_id ; All_qcls::_id]

                    commit record

                    script to make it toggle? Also, it would be nice to have the ones being shown to change colors--obviously i'm speaking of conditional formatting. How would this work?

                    I'm also having some difficulty understanding how this works. How can the frequency indicator calculation be that? I dont' really understand what's happening here.

                    • 7. Re: Graph values from a selectable list of records from another table

                      "Toggle" assumes a two value set, but we have an entire list of qcls records to pick from. Do you mean if you have selected a Qcls record by clicking it, clicking the same portal row clears the qcl_id field?

                      If so:

                      Set Field [atmospheric_data::qcl_id ; If ( atmospheric_data::qcl_id ≠ All_qcls::_id ; All_qcls::_id ) ]

                      You can use this expression as a conditional format expression:

                      Not IsEmpty ( FilterValues ( atmospheric_data::qcl_id ; All_qcls::_id ) )

                      The FilterValues option insures that this works even if you have clicked the select all button.

                      One cute trick you can try is to put a rectangle object in the portal row and then use layout text to put an X or a "tick mark" character on top of it. Then use conditional formatting to change the font size to 500 to make the layout text invisible when that portal row is not selected. You'd need to remove the "not" operator from the above function if you choose to use this method. This creates what looks like a set of check boxes where you can click to select a record.

                      I'm not suprised that you are having difficulty understanding the frequency indicator calculation. There are two different advanced database concepts working in concert to make it work.

                      First, when you put a list of values separated by returns into a field used as the key in a relationship, any one of the values may match to any one value in the key field on the other side of the relationship. If you have three Qcls records numbered 23, 45 and 70. Putting all three values in such a list like we do here means that your current record matches to any record in Qcls numbered 23 OR 45 OR 70.

                      Second, the relationship uses inequalities to match a given wavenumber in THz units to a value range (min and max frequencies in THz) on the other side. If the waveNumber falls in the range of a related qcls record, the expression returns True. This uses the relationship to recreate what you originally had in your If function so that it can work with the list of all Qcls records when the select all script puts all of them into the global field. A value of True, BTW returns the number 1 and False returns 0 so this is why there's no explicit reference to a 1 or 0 in the expressions I used.