General Form Query

Hi. I have a form for capturing data for Timber Moisture Content and one of the fields is Date Ready for Treatment. If we take an example of 100 Bales of timber are checked for moisture, 20 are suitably dry so the user records the Date Ready for Treatment. Next week, of the remaining 80 bales that are checked a further 20 bales are ready. Is there a way I can record the date ready for treatment for the second 20 while maintaining the date ready for treatment for the first 20, all on the same record.

Solved Solved
0 37 1,742
1 ACCEPTED SOLUTION

@MauriceWhelan
In my sample app, [DATE_CHANGES] column is an ENUMLIST type with both Allow other values and AutoCompleteOtherValues properties are set to ON. Item separator is set to " , " (space comma space), Base type is Date. The below expression is in AppFormula:

=SPLIT(LOOKUP([_THISROW].[ID],"Record Changes","ID","CHANGED_DATES"))+
IFS(NOT(IN([DATE_FOR_TREATMENT],SPLIT(LOOKUP([_THISROW].[ID],"Record Changes","ID","CHANGED_DATES")))),
LIST([DATE_FOR_TREATMENT]))

View solution in original post

37 REPLIES 37

LeventK
Participant V

@MauriceWhelan
Will you be creating new records for each or will you be editing an existing record?

Edit existing record each time

@MauriceWhelan
Provided you donโ€™t mind the data having in comma separated form i.e. โ€œ3/2/2019 , 3/3/2019 , 3/4/2019โ€, yes itโ€™s possible. Firstly add a change timestamp type column, bind it to [Date Ready for Treatment] column for capturing each change in this column. Then you can CONCATENATE these change timestamp values for that record.

Thank you very much @LeventK I will take a look

@MauriceWhelan
I have build a sample app for your use case. You can check the Record Changes app from my portfolio page.
https://www.appsheet.com/portfolio/245151

After testing, provided the proposed solution fits your needs, please visit this post again and from this display, click the green checkbox next to your name in the main menu so that it will mark your proposal as SOLVED and your link will be filtered away from this sample app.

Thanks a million @LeventK. I will take a peek.

LeventK
Participant V

Youโ€™re welcome @MauriceWhelan, enjoy.

Hi @LeventK. I am trying to incorporate a CONTAINS Expression into an OR Expression but canโ€™t seem to get the syntax quite right. I have OR(CONTAINS([Module] = โ€œWorks Ordersโ€), ([Module] = โ€œProductionโ€), ([Module] = โ€œRetentionโ€)) which I know is a little off. Could you advise on the right structure?

@MauriceWhelan

OR(
CONTAINS([Module], โ€œWorks Ordersโ€),
CONTAINS([Module], โ€œProductionโ€),
CONTAINS([Module], โ€œRetentionโ€)
)

Thanks so much

Youโ€™re welcome @MauriceWhelan, enjoy.

Hi @LeventK. I have added in the columns as you advised and used your formula in the Date Ready for Treatment Changes. My formula reads SPLIT(LOOKUP([_THISROW].[Record ID],โ€œMoisture Check Timberโ€,โ€œRecord IDโ€,โ€œDate Ready for Treatment Changesโ€))+
IFS(NOT(IN([Date Ready for Treatment],SPLIT(LOOKUP([_THISROW].[Record ID],โ€œMoisture Check Timberโ€,โ€œRecord IDโ€,โ€œDate Ready for Treatment Changesโ€)))),
LIST([Date Ready for Treatment])) but the form is not showing the Date Ready for Treatment Changes field. Hoping you could advise? Thanks.

Hello @MauriceWhelan
Are you using a slice with your form? Provided you are using a slice, endure that your [Date Ready for Treatment Changes] column is included in the slice.

Hi @LeventK. Thank you for the response. I have it working a little. Still not happy with the behaviour. Couple of issues I am having

  1. I noticed in your sample app that when you select the new DATE_FOR_TREATMENT it updates the DATE_CHANGES field within the form. In mine it only seems to add in the new DATE_FOR_TREATMENT after I save the record. When I return to the record to add another DATE_FOR_TREATMENT value the DATE_CHANGES field is empty on the form.
  2. I want the format of the value in the DATE_CHANGES field to be DD/MM/YYYY like your sample but it is appearing as โ€˜Fri Mar 29 2019 00:00:00 GMT+0000 (Greenwich Mean Time)โ€™ in mine.

Could you help?

@MauriceWhelan
In my sample app, [DATE_CHANGES] column is an ENUMLIST type with both Allow other values and AutoCompleteOtherValues properties are set to ON. Item separator is set to " , " (space comma space), Base type is Date. The below expression is in AppFormula:

=SPLIT(LOOKUP([_THISROW].[ID],"Record Changes","ID","CHANGED_DATES"))+
IFS(NOT(IN([DATE_FOR_TREATMENT],SPLIT(LOOKUP([_THISROW].[ID],"Record Changes","ID","CHANGED_DATES")))),
LIST([DATE_FOR_TREATMENT]))

Hi @LeventK. Think it is sorted now. Looks like it is working correctly now Many thanks again for your help. It really makes a difference to get help on these things.

Hello @MauriceWhelan, glad to hear that you have solved it. Truly my pleasure to be helped of. Can you please mark my post as solution provided it suits your requirements and solved the issue so that other members can also benefit out of it when they search for the โ€œsolutionโ€ tag in the community. Thanks.

Hi @LeventK. If I have [Description] field in 2 different tables, is there a way I can compare these 2 columns and present the differences for a specific date period? Apologies if I should be directing this as new topic.

Hi @MauriceWhelan
Can you please elaborate your query a bit more? Yes of course you can compare values from 2 columns from different tables conditionally, but provided you can be specific, I will try to help more. Thanks.

Thank you @LeventK. I have 1 table called (Production List) and another called (Create Works Order) both with a column [Description]. In theory the data captured in the PL table should dictate what Works Ordersโ€™s are entered in the CWO table. So what I want to do is interrogate the [Description] Column in both tables and create a report displaying both columnโ€™s values and some way to highlight the differences in the 2 columns by date. Hope that helps a little.

@MauriceWhelan
Is there any kinda dependency or reference between these tables? How will you be creating that report? Scheduled? On-demand with workflow?

@LeventK
I am thinking a scheduled report once a week. There is no dependency between the tables.

@MauriceWhelan
Both me and my brain cells are a bit tired for todayโ€ฆI need to think about this a little bit and then we can discuss on it. Thanks for your understanding.

@LeventK
No problem. Me too. Thank you for taking the time to help.

@MauriceWhelan - i am no where near as experienced as @LeventK , but maybe some quick questions may help lead to a solution. Can you clarify the data structure a little bit (what fields you have in each table)? If as you say you want to โ€œinterrogateโ€ the [Description] column in each of the two tables for differences, it makes me wonder why these two tables donโ€™t have a relationship tying the record together.

I am sure the two [Description] columns can be compared, but if you are looking to see if they match, can you clarify why you want them to match if the tables arenโ€™t related? Orโ€ฆ what you want to do if they donโ€™t match. Just trying to understand your data structure betterโ€ฆ

Hi @Mike The Production List Table contains [Product Description] with a couple of other fields that donโ€™t really matter in this case. The user completes a form for every row of product to be treated. In theory these rows are what should be entered on the W/O form for each Product (this is probably where you think the relationship should be) but it regularly changes so the user needs to be able to select any product here. So when the user enters all the W/O rows I would like to compare the [Product Description] Column in both tables and somehow identify the rows that donโ€™t match. I am not to concerned about how I show the differences. Maybe you guys could advise a good display. Hope that makes sense.

@MauriceWhelan - if you really have two arbitrary table with the relationship between them Iโ€™m not sure how youโ€™re going to show differences between records, as for rows arenโ€™t related.

If you were using Google sheets as a source you could consider just adding another tab to provide a side-by-side list of the descriptions? Pretty easy to use Find, Match or Vlookupโ€™s to identify descriptions that are identical .

Not sure if this helps you, but Iโ€™m having trouble envisioning two tables that donโ€™t relate needing to be matchedโ€ฆ

Hi @Mike. Thank you for that. I was thinking of the Google Sheets route as you described as a backup and then maybe create a view in appsheet.

@MauriceWhelan - thatโ€™s how i would approach it, and then itโ€™s easy to โ€œdisplayโ€ the results back in you app. Hope that helped solve the dilemma.

LeventK
Participant V

@MauriceWhelan
You can add 2 separate tables to your workflow template and use 2 separate <<Start>><<End>> expression to gather data from 2 distinct tables. For example:

@Mike & @LeventK
Great feedback, thanks a million. I will test out each scenario and see what works best.

@MauriceWhelan
@Mike
You should consider that, creating a separate tab to concatenate data from distinct tables with sheetformulas especially with the expensive ones like QUERY, IMPORTRANGE,VLOOKUP etc. will increase the appโ€™s sync time. Provided the intention is only gathering data for reporting, you can easily do that with workflows already. Therefore, provided you do not require a complex table/data structure within your report, this option will work more than fine.

@LeventK - thanks for the insights. Can you clarify, if the tab that does the vlookups is not actually included as a table in Appsheet, that should not effect sync speed right? I often do some back-end analytics or re-structuring, and then only connect the results tables to Appsheet. For example, I might have a tab (call it table1) that pulls data from multiple sources and performs a bunch of calculations across a large data set. I then might have a summary tab using a gSheet Query (call it Table2) and thatโ€™s what I connect to Appsheet as a source.

May not been good approach in this case, but I was assuming that would protect sync speed. Can you confirm only Table2 effects the sync speed?

@Mike
Include it or not, AppSheet always bulk reads the Spreadsheet as a whole workbook. Therefore I may advise removing/detaching any unused/unrelated sheet tabs from the appโ€™s gSheet back-end.

@LeventK - thanks! For the future, I will either handle directly in Appsheet, or keep my backend analysis sheets in an entirely separate google sheet and only link the sheet I need for Appsheet to consume. Thanks for the advice!

Thank you for that advice @LeventK. For some reason I am only seeing this reply this evening.

Hi @LeventK. I am attempting to implement the above but something is confusing me a little. I have the template setup as you suggested and have created a Scheduled Report (because I only want to run the comparison once a week automatically). Like the workflow, however, the report facility only allows you to select 1 table to create the report for. Am I correct in saying I just chose either table in the Report setup and the select expression in the template will pull the data from both tables. Hope that makes sense.

Top Labels in this Space