Comparison between two tables and display the outcome on one column using the API_DoQuery

Question

Good DayI am an intern at an ISP assigned to develop a Reconciliation between bank deposits and the amount floated to vendors. I am having a problem with comparing the two different fields and displaying the corresponding value in the Reconciliation table onto a specific column. I have tried to apply the API_DoQuery which gives no errors but doesn't write the data into the other table. Please assist! Here is the function i have applied which doesn't include the field ID of the column to display the data. I have tried using the IF statement formula but i have no option to import from another table.&lt;qdbapi&gt; &lt;usertoken&gt;%user.token.b4s2ci_jdr8_brbx.VenFloat%&lt;/usertoken&gt; &lt;apptoken&gt;app_token&lt;/apptoken&gt; &lt;udata&gt;mydata&lt;/udata&gt; &lt;query&gt;{'7'.EX.'_FID_6'}AND{'27'.EX.'_FID_16'}&lt;/query&gt;&lt;/qdbapi&gt;------------------------------Daniel Othwolo------------------------------

austink · Answer

DoQuery will only do a query that you choose and return data. It will return everything you ask it to and then you need to do something with that data.

After the DoQuery how are you handling the returned data?

If you are wanting to get data from one table and import it to another one you are likely wanting to use ImportFromCSV but that qill require the data to be in csv format.

danielothwolo · Answer

Yes, I intend to import data into one table from a CSV file then map one column onto another table if the criteria are met using the ImportFromCSV. I did attempt to do that but the results don't seem to display on the other table. How can I add an IF Statement to compare records and add the data where the criteria is met?

------------------------------
Daniel Othwolo
------------------------------

danielothwolo · Answer

Please help!!!!!------------------------------Daniel Othwolo------------------------------

austink · Answer

Just so I understand a little better, do you have any code that you are already using? I'm not even 100% sure what you are doing from your explanation.One thing I was thinking is, do you need to do this via code or have you tried a native solution? If you need 2 tables data sent to a single table that is easy and you can then do whatever calculations or comparisons you want. You just need to use relationships and have the 2 tables be parents and have one child table where you lookup fields to. Unless I am misunderstanding what you are wanting to do here?

danielothwolo · Answer

Thank you. I am developing an application that has five tables. An agent is has a one to many relationship to a float table where floats are transferred to agents and once the previous float has been cleared, they can get more, this data is input one by one therefore sending a corresponding value to the Reconciliation table. These agents do get customers and they can only where they sell tokens and get the money in return via Cash or Cheque and deposit into the bank. These types of deposits have been associated into two separate tables. The final table is the Reconciliation table that flags any float that has not been deposited onto the bank. Bank records are uploaded once every day from a spreadsheet into the Cash/ Cheque tables and should get the deposit amount into the Reconciliation table using the ImportFromCSV_API&nbsp;This should be matched with the date and reference number in the Reconciliation table to add the deposit amount. My challenge is it dumps all the records without matching any values in the Reconciliation table. There is no relationship between the Reconciliation and Banc/ Cheque tables respectively.I have not applied any code but i am planning to use javascript and add the query to match the criteria. How can I apply this?------------------------------Daniel Othwolo------------------------------

Forum Discussion

Comparison between two tables and display the outcome on one column using the API_DoQuery

Related Content

Table Comparisons

Hour Comparison

Create a Comparison Column in Summary Reports

Formula Text - using string comparison operators - not working

Week to Week Comparisons