Answers

• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  July 2023

  Unusual .

  Usually such a cross link wouldn't be possible, however, in this case if it is just a list of options, what to do is instead of using:

  ->options( 'toc_customers', 'tax_registration_number', 'business_name' )
  

  Use a function to get the options. Since it is an option, you can get the data from anywhere, including an external database connection. Just make sure you return an array of associative arrays that contain value and label properties (like in the linked documentation).

  Allan
• tiago.fernandes Posts: 13Questions: 4Answers: 0

  July 2023 edited July 2023

  Hi allan,

  I don't know if I explain myself correcty this cross link isn't connected into a external database, it's connected to a dummy table inside my mysql database. My idea is to replace this code into one that connects to an external table.
  I'll take a look into the custom function.
  Thank you!
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  July 2023

  this cross link isn't connected into a external database, it's connected to a dummy table inside my mysql database

  I don't quite understand I'm afraid. Your first post suggested your main table was MySQL, and you wanted to get the options list from an SQL Server db. Is that not the case?

  Allan
• tiago.fernandes Posts: 13Questions: 4Answers: 0

  July 2023

  Yes that was the case, sorry if I confused you.
  I solved the problem by using a sqlsrv_query inside a closure function, as you suggested.
  Working code below:

  Field::inst( 'customer_id' )
          ->options( function () use ($conn) {
              $sql = "SELECT ID, Name FROM.[dbo].[Customers] ORDER BY Name ASC";
              $stmt = sqlsrv_query( $conn, $sql );
              $data = array();
              while( $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) ) {
                  $data[] = array(
                      'value' => $row['ID'],
                      'label' => $row['Name ']
                  );
              }
              return $data;
          }),
  

  Thank you for you help.
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  July 2023

  Nice one - glad to hear you've got it working!

  Allan