Discussions

 View Only
  • 1.  Looking to extract data from a text field

    Posted 03-09-2023 11:45
    Edited by Joe Alderson 03-09-2023 12:18

    Hi all,

    I'm looking for a way to extract text from the middle of a text field where there's a match to data in another field. As an example say I have a record with a field for Tax Year Ending which will be 4 digits i.e. 2021 or 2022 etc.

    I need to use that field to find matches in another field which will have an ID number followed by a / and then a tax year, but it can have more than one of these.

    For example if this other field (let's call it Summary) contains the following:

    "16/2023 ; 9/2022"

    And the Tax Year Ending in the record is 2022, I need another field to extract "9" from the Summary. If the Tax Year Ending was 2023 then it would need to extract "16" from the Summary.

    If it's easier to do then it could extract "9/2022" or "16/2023" respectively but it would be cleaner to skip that step if possible.

    Many Thanks,

    Joe



    ------------------------------
    Joe Alderson
    ------------------------------



  • 2.  RE: Looking to extract data from a text field

    Posted 03-09-2023 17:52
    Edited by Mike Tamoush 03-09-2023 17:52

    Can we assume the Summary field will only contain one instance of each year? Meaning, this will never occur: "16/2023 ; 9/2023; 19/2022"

    Where we have two instances of 2023?

    And what are the max number of parts in the summary field? Will you ever have 50 or 60 parts? Or always say, less than 15?

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 3.  RE: Looking to extract data from a text field

    Posted 03-10-2023 04:13
    Edited by Joe Alderson 03-10-2023 04:13

    Hi Mike,

    Yes that's correct, there would only ever be one instance of a tax year in the Summary field, so you would never see the same tax year twice.

    I could adjust the data in the Summary field to read 2022/9/2022 ; 2023/16/2023. Would that make it easier to extract the text between the tax year?

    A safe cap would be 30 parts in the Summary field maximum, though it's unlikely to reach that. Probably in practice 20 would be the maximum we would see.



    ------------------------------
    Joe Alderson
    ------------------------------



  • 4.  RE: Looking to extract data from a text field

    Posted 03-10-2023 17:59
    Edited by Mike Tamoush 03-10-2023 18:00

    I would use the Part function, as well as some Left function. Would be a long code, but repetitive so not too bad. 

    So your field that is extracting your 9 or 16 would look like this

    var text TaxYear = [Tax Year Field];

    var text SummaryField = ToText([The Summary Field]); //this only needs the ToText if it is a combined text field or something other than text

    var text PartOneSummary = Trim(Part($Summary, 1, ";" ));
    var text PartTwoSummary = Trim(Part($Summary, 2, ";" ));
    .
    .
    var text PartThirtySummary = Trim(Part($Summary, 30, ";" ));

    If(
    contains($PartOneSummary, $TaxYear), Left(PartOneSummary, "/"),
    contains($PartTwoSummary, $TaxYear), Left(PartTwoSummary, "/"),
    .
    .
    contains($PartThirtySummary, $TaxYear), Left(PartThirtySummary, "/")
    )



    ------------------------------
    Mike Tamoush
    ------------------------------



  • 5.  RE: Looking to extract data from a text field

    Posted 03-13-2023 08:50

    Thanks Mike, that code makes sense and seems to be working a charm.

    Much appreciated.

    Joe



    ------------------------------
    Joe Alderson
    ------------------------------